You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@devlake.apache.org by "danielemoraschi (via GitHub)" <gi...@apache.org> on 2026/04/13 16:45:17 UTC
Re: [PR] feat(grafana): jira sprint based dashboards [incubator-devlake]
danielemoraschi commented on PR #8574:
URL: https://github.com/apache/incubator-devlake/pull/8574#issuecomment-4238108923
Hi,
Just recently came across this thread and wanted to share how I have been reporting Sprints in my instance.
A key difference in my approach is that I use `issue_changelogs` to reconstruct each issue's status and story points as they were at sprint end, rather than using `i.status` (the issue's current status). Without that, an issue completed after the sprint closed still shows as "delivered" for that sprint, which can skew delivery rates over time. I also track scope changes, issues added or removed mid-sprint, using the Sprint field changelog.
Full query below, if of any help.
You might have to adapt filters as needed.
```sql
WITH sprint_data AS (
-- Get sprint info and explicitly filter to the current Board loop
SELECT DISTINCT
s.id AS sprint_id,
s.name AS sprint_name,
s.started_date,
s.completed_date
FROM sprints s
JOIN boards b ON s.original_board_id = b.id
WHERE
s.name REGEXP CONCAT('^', '${Sprints:regex}')
AND $__timeFilter(s.started_date)
-- This restricts the sprints to the board currently being rendered
AND (b.name IN ("${Boards}") OR "${Boards}" = '')
),
sprint_issues_base AS (
SELECT
j.id AS issue_id,
j.issue_key,
j.status AS current_status,
j.story_point AS current_story_points,
j.original_type,
j.assignee_name,
j.original_project,
sd.sprint_id,
sd.sprint_name,
sd.started_date,
sd.completed_date
FROM issues j
JOIN sprint_issues spi ON spi.issue_id = j.id
JOIN sprint_data sd ON spi.sprint_id = sd.sprint_id
LEFT JOIN board_issues boi ON boi.issue_id = j.id
LEFT JOIN boards bo ON bo.id = boi.board_id
WHERE j.original_type NOT IN ('Epic', 'Sub-task')
AND (j.assignee_name IN ($Person) OR $Person = '')
AND (bo.name IN ("${Boards}") OR "${Boards}" = '')
AND j.original_project = "${JiraProjects}"
),
added_to_sprint AS (
SELECT
sib.issue_id,
sib.sprint_id,
sib.issue_key,
sib.current_status,
sib.current_story_points,
TIMESTAMP(sib.started_date) as time,
sib.sprint_name,
sib.started_date,
sib.completed_date,
-- Find the first changelog entry where this sprint was added to the issue
(SELECT MIN(icl.created_date)
FROM issue_changelogs icl
WHERE icl.issue_id = sib.issue_id
AND icl.field_name = 'Sprint'
AND icl.original_to_value LIKE CONCAT('%', sib.sprint_id, '%')
-- And the sprint was NOT in the "from" value (i.e., it was actually added, not just moved)
AND (icl.original_from_value NOT LIKE CONCAT('%', sib.sprint_id, '%') OR icl.original_from_value IS NULL)
) AS added_date,
NULL as removed_date
FROM sprint_issues_base sib
),
removed_from_sprint AS (
SELECT
j.id AS issue_id,
sd.sprint_id,
j.issue_key,
j.status AS current_status,
j.story_point AS current_story_points,
TIMESTAMP(sd.started_date) as time,
sd.sprint_name,
sd.started_date,
sd.completed_date,
NULL as added_date,
icl.created_date AS removed_date
FROM issues j
JOIN issue_changelogs icl ON icl.issue_id = j.id
JOIN sprint_data sd ON 1=1
LEFT JOIN board_issues boi ON boi.issue_id = j.id
LEFT JOIN boards bo ON bo.id = boi.board_id
WHERE
-- Sprint field change
icl.field_name = 'Sprint'
-- Was in this sprint (from value contains sprint ID)
AND (icl.original_from_value LIKE CONCAT('%', sd.sprint_id, '%') OR icl.original_to_value IS NULL)
-- Was removed from this sprint (to value does NOT contain sprint ID or is null)
AND (icl.original_to_value NOT LIKE CONCAT('%', sd.sprint_id, '%') OR icl.original_to_value IS NULL)
-- Removal happened during the sprint window
AND icl.created_date >= sd.started_date
AND (icl.created_date <= sd.completed_date OR sd.completed_date IS NULL)
-- Filters
AND j.original_type NOT IN ('Epic', 'Sub-task')
AND (bo.name IN ("${Boards}") OR "${Boards}" = '')
AND j.original_project = "${JiraProjects}"
-- Ensure issue is NOT currently in the sprint (wasn't re-added)
AND NOT EXISTS (
SELECT 1 FROM sprint_issues spi
WHERE spi.issue_id = j.id AND spi.sprint_id = sd.sprint_id
)
),
all_sprint_issues AS (
SELECT
asp.* FROM added_to_sprint asp
UNION ALL
SELECT
rsp.* FROM removed_from_sprint rsp
),
status_at_sprint_end AS (
SELECT
asi.*,
-- Get the most recent status BEFORE or AT the reference date
(SELECT icl.to_value
FROM issue_changelogs icl
WHERE icl.issue_id = asi.issue_id
AND icl.field_name = 'status'
AND icl.created_date <= asi.completed_date
ORDER BY icl.created_date DESC
LIMIT 1
) AS last_status_before_sprint,
-- Get the FIRST status change ever (to determine initial state)
(SELECT icl.from_value
FROM issue_changelogs icl
WHERE icl.issue_id = asi.issue_id
AND icl.field_name = 'status'
ORDER BY icl.created_date ASC
LIMIT 1
) AS initial_status
FROM all_sprint_issues asi
),
status_at_sprint_end_final AS (
SELECT
sase.*,
COALESCE(last_status_before_sprint, initial_status, "TODO") as status_in_sprint
FROM status_at_sprint_end sase
),
story_points_at_sprint_end AS (
SELECT
asi.*,
COALESCE(
-- If there was a change AFTER reference date, get the "from" value of the earliest change
(SELECT CAST(icl.original_to_value AS DECIMAL(10,1))
FROM issue_changelogs icl
WHERE icl.issue_id = asi.issue_id
AND icl.field_name = 'Story Points'
AND icl.created_date <= asi.completed_date
ORDER BY icl.created_date DESC
LIMIT 1),
(SELECT
CASE
WHEN icl.original_from_value IS NULL OR TRIM(icl.original_from_value) = ''
THEN 0 -- Story points were NULL at sprint end (ticket had no points initially)
ELSE CAST(icl.original_from_value AS DECIMAL(10,1)) -- Use the from_value as initial
END
FROM issue_changelogs icl
WHERE icl.issue_id = asi.issue_id
AND icl.field_name = 'Story Points'
ORDER BY icl.created_date ASC
LIMIT 1),
asi.current_story_points
) AS historical_story_points
FROM status_at_sprint_end_final asi
)
SELECT * FROM story_points_at_sprint_end ORDER BY started_date ASC
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org