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