How to get Story Points Sumed up by Status?

[CCurrier]

Looking for help or suggestions on how to get story points Sumed up by status within a two week time box.

[CCurrier · Accepted solution]

I actually figured this out. Below is a screenshot of what I did.

[Foster Cryer]

@CCurrier - Here is a little bit of a complex query, which is for an older set of data that starts in 2021; you can adjust the dates to be for the year your Jira data starts with.

This is based on the creation date within Jira, but you can modify that to be for a different date field.

NOTE: Please verify the query's results before implementing them in a production dashboard. This is an example query that will likely need to be modified further to meet your needs.

SELECT
DATE_ADD('2021-01-01',
CAST(
FLOOR(
(
(CAST(SUBSTRING(`Issue`.`created_at`, 1, 4) AS INT) - 2021) * 365 +
(CAST(SUBSTRING(`Issue`.`created_at`, 6, 2) AS INT) - 1) * 30 +
CAST(SUBSTRING(`Issue`.`created_at`, 9, 2) AS INT)
) / 14
) * 14 AS INT
)
) AS period_start_date,
DATE_ADD('2024-01-01',
CAST(
FLOOR(
(
(CAST(SUBSTRING(`Issue`.`created_at`, 1, 4) AS INT) - 2021) * 365 +
(CAST(SUBSTRING(`Issue`.`created_at`, 6, 2) AS INT) - 1) * 30 +
CAST(SUBSTRING(`Issue`.`created_at`, 9, 2) AS INT)
) / 14
) * 14 + 13 AS INT
)
) AS period_end_date,
`Issue`.`status` AS Status,
SUM(CAST(`jira_issue_custom_fields`.`Story Points` AS FLOAT)) AS `total_story_points`
FROM `jira_issue` AS `Issue`
INNER JOIN
(SELECT *
FROM
(SELECT `issue_id`,
`name`,
`value`
FROM `jira_issue_field`) PIVOT (MAX(`value`)
FOR `name` IN ('Story Points'))) AS `jira_issue_custom_fields` ON `jira_issue_custom_fields`.`issue_id` = `Issue`.`issue_id`
GROUP BY
period_start_date,
period_end_date,
Status
ORDER BY
period_start_date,
Status
LIMIT 100000;

 

[Foster Cryer]

@CCurrier - for the two week timeframe, what information do you want to group by? Last updated date? Accepted/completion date? Sprint dates, or something else?

[CCurrier]

Hi Foster Cryer,

I currently get this data using the issue navigator (JQL below) but I want to have this info displayed on a Analytics Dashboard.

 JQL I use - status changed to (accepted,completed,closed) DURING ("X","X").

To best see were everything sits for a given team, "last updated Date" would be best for this scenario