Page Comparison

...

Standard Time in Status metric works excellent excellently in most cases. But what if you want to analyze specifically what's happening in your sprints you might want to have a custom chart. Here is how to create it.

...

This metric is based on Task Tracking System's (Jira, Azure Boards or Rally) data.
Tickets assigned to sprints are taking taken into account.
Metrics doesndon't calculate time in Done Statuses (set in configuration)
Main calculation logic: T status = (Timestamp a ticket left the status - Timestamp a ticket put in the status). Also metric takes into account when ticket has been assigned to a sprint & whether it left a sprint before its end.
1. Example: ticket created & assigned to a sprint during a sprint in To Do status, spent 1 day & got unassigned. In this case time in To Do status for this ticket for that sprint will be 1 day.
2. Example: ticket created & assigned to a sprint before its start & spent the whole sprint in To Do status till the end. Time in status will be equal to sprint duration (+ time from creation to sprint assignment for calculation logic 1) in this case.
There are 2 types of calculation logic - whether to take into account time spent in status before sprint assignment or not. In 4b example if we want to take into account time from creation to sprint assignment then we use logic 1, if not - logic 2.
1. Use logic 1 if you want to see what happened before assignment to a sprint - e.g. it could reveal carry-overs
2. Use logic 2 if you want to see where your ticket spend spends more time within an a sprint.
In case of re-opens metric calculate sum of time spent in the same statuses.
There are options to aggregate values in a median, average or cummulative cumulative way.
Time in status is measured in days.

...

Configuration

We have 4 6 variables in the code of this metric:

...

Code Block

language	sql

with number_of_sprints_to_show as
(
    selectselect 6 --how many sprints to show on the chart
  as  as show
),
  
done_statuses as --statuses considered closed/done/completed
(
select 'Done' as dst
),
  
median_average_or_cumulative as
(
select 1 --1=median time in status
         
         --2=average time in status
                  --3=cumulative time in status
),
  
calculation_logic as
(
select 1 --1=calculate whole time in status (time within a sprint + time ticket spent in status before that (if any))
         
         --2=calculate time in status within particular sprint
),
  
scope as --scope for calculation
(
select * from ticket
),
  
sprints as --selecting last N sprints
(
    selectselect * from sprint
  where  where start_date<now()
  order  order by start_date desc
   limit limit (select show from number_of_sprints_to_show)
),
  
status_history as --history of statuses
(
  select   
  select    a.id as workitem_id,
            coalesce
            coalesce(b.start,a.created) as start,
            coalesce            coalesce(b.status,a.status) as status,
            ROW
            ROW_NUMBER() OVER (PARTITION BY workitem_id ORDER BY start asc) as history_id
    fromfrom scope a
  left  left join tickethistory b on a.id=b.workitem_id and b.field=0
),
  
last_status as --searching for the last status in history for a ticket
(
  select   
  select    workitem_id,
            max
            max(history_id) as history_id
    fromfrom status_history
    groupgroup by workitem_id
),
  
status_history_with_last as --enrich status_history table with last status info
(
  select  select a.workitem_id, a.start, a.status, a.history_id, b.history_id as last_status,
  case  case when b.history_id is not null then a.start else null end as last_status_date
    fromfrom status_history a
  left  left join last_status b on a.workitem_id=b.workitem_id and a.history_id=b.history_id
),
  
sprint_history as --history of sprint assignments
(
   select             a.idselect
as workitem_id, a.           a.id as workitem_id, a.key, a.summary, a.type, a.priority,
            coalesce
            coalesce(b.start,a.created) as start,
            coalesce            coalesce(b.sprint,a.sprints::text) as sprint,
            ROW            ROW_NUMBER() OVER (PARTITION BY workitem_id ORDER BY start asc) as history_id
  from scope from scope a
  left  left join tickethistory b on a.id=b.workitem_id and b.field=1
),
  
sprint_dates as --start & finish dates for sprint assignments
(
select         workitem
  		workitem_id, key, summary, type, priority,
        id  		id, name, start_date, complete_date,
        start,         case when		start, finish_<start then coalesce(complete_date,now())
else finish_ endfrom
as( finish --case
when there wasselect a
re-asign from a sprint before sprint start date
  from
( 
  select
        a		a.workitem_id, a.key, a.summary, a.type, a.priority,
          s		s.id, s.name, s.start_date, s.complete_date,
          greatest		greatest(max(b.start),s.start_date) as start,
--start of assignment is the latest of last assignment (in case there were multiple) and sprint start date
        least(min(c.start),coalesce(s.complete_date,now())) as finish_ --finish of assignment is the earliest of first re-assignment and sprint end date (or now in case of active sprint)
from sprint_history a
join sprints s on a.sprint 		least(min(c.start),coalesce(s.complete_date,now())) as finish
from sprint_history a
join sprints s on a.sprint like '%'||s.id||'%'
left join sprint_history b on a.workitem_id=b.workitem_id and a.history_id>b.history_id and b.sprint not like '%'||s.id||'%'
left join sprint_history c on a.workitem_id=c.workitem_id and a.history_id<c.history_id and c.sprint not like '%'||s.id||'%'
leftgroup join sprint_history b on by a.workitem_id=b.workitem_id and a.history_id>b.history_id and b.sprint not like '%'||s.id||'%'
left join sprint_history c on a.workitem_id=c.workitem_id and a.history_id<c.history_id and c.sprint not like '%'||s.id||'%'
group by a.workitem_id, s.id, s.start_date, s.complete_date, s.name, a.key, a.summary, a.type, a.priority
) as temp
),
 
, s.id, s.start_date, s.complete_date, s.name, a.key, a.summary, a.type, a.priority
) as temp
  where finish>start --case when there was a re-asign from a sprint before sprint start date
),
 
final_calculations as --joining sprint dates & statuses to get duration of time spent in each status within each sprint
(
  select         a.workitem_id, a.key, a. select
        a.workitem_id, a.key, a.summary, a.type, a.priority,
        a
        a.id, a.name, a.start_date,
        b
        b.status,
        sum
        sum(case when (b.history_id=c.last_status) and (select * from calculation_logic)=1 then --status=current status + logic=1
             DATE             DATE_PART('day', a.finish-b.start)
                        +DATE_PART('hour', a.finish-b.start)/24.0
            
            +DATE_PART('minute', a.finish-b.start)/1440.0
        when
        when (b.history_id=c.last_status) and (select * from calculation_logic)=2 then --status=current status + logic=2
             DATE             DATE_PART('day', a.finish-greatest(b.start,a.start))
                        +DATE_PART('hour', a.finish-greatest(b.start,a.start))/24.0
            
            +DATE_PART('minute', a.finish-greatest(b.start,a.start))/1440.0
        when
        when (b.history_id!=c.last_status) and (select * from calculation_logic)=1 then --status!=current status + logic=1
             DATE
             DATE_PART('day', least(c.start,a.finish)-b.start)
                        +DATE_PART('hour', least(c.start,a.finish)-b.start)/24.0
            
            +DATE_PART('minute', least(c.start,a.finish)-b.start)/1440.0
          else                                                                            --status!=current status + logic=1
             DATE_PART('day', least(c.start,a.finish)-greatest(b.start,a.start))
            +DATE_PART('hour', least(c.start,a.finish)-greatest(b.start,a.start))/24.0
            +DATE_PART('minute', least(c.start,a.finish)-greatest(b.start,a.start))/1440.0
        end) as duration
from sprint_dates a
  join status_history b on a.workitem_id=b.workitem_id
    and lower(b.status) not in (select lower(dst) from done_statuses)
    and b.start<=a.finish
  join status_history_with_last c on a.workitem_id=c.workitem_id and
        ((b.history_id+1=c.history_id and c.start>a.start) OR (b.history_id=c.last_status))
  group by a.workitem_id, a.key, a.summary, a.type, a.priority, a.id, a.name, a.start_date, b.status
)
 
select  name as "Sprint Name",          --change statuses below according to your workflow
        case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY to_do))
             when (select * from median_average_or_cumulative)=2 then avg(to_do)
                                                                 else sum(to_do) end as "To Do",
        case      else                                                                            --status!=current status + logic=1
             DATE_PART('day', least(c.start,a.finish)-greatest(b.start,a.start))
            +DATE_PART('hour', least(c.start,a.finish)-greatest(b.start,a.start))/24.0
            +DATE_PART('minute', least(c.start,a.finish)-greatest(b.start,a.start))/1440.0
        end) as duration
from sprint_dates a
  join status_history b on a.workitem_id=b.workitem_id
    and lower(b.status) not in (select lower(dst) from done_statuses)
    and b.start<=a.finish
  join status_history_with_last c on a.workitem_id=c.workitem_id and
        ((b.history_id+1=c.history_id and c.start>a.start) OR (b.history_id=c.last_status))
  group by a.workitem_id, a.key, a.summary, a.type, a.priority, a.id, a.name, a.start_date, b.status
)
 
select  name as "Sprint Name",          --change statuses below according to your workflow
        case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY to_do))
             when (select * from median_average_or_cumulative)=2 then avg(to_do)
                                                                 else sum(to_do) end as "To Do",
        case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY selected_for_development))
             when (select * from median_average_or_cumulative)=2 then avg(selected_for_development)
                                                                 else sum(selected_for_development) end as "Selected for Development",
        case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY blocked))
             when (select * from median_average_or_cumulative)=2 then avg(blocked)
                                                                 else sum(blocked) end as "Blocked",
        case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY in_progress))
             when (select * from median_average_or_cumulative)=2 then avg(in_progress)
                                                                 else sum(in_progress) end as "In Progress",
        case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY selectedcode_for_developmentreview))
             when
             when (select * from median_average_or_cumulative)=2 then avg(selectedcode_for_developmentreview)
                                                                 else sum(selected_for_development) end as "Selected for Development",
        case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY blocked))
             when (select * from median_average_or_cumulative)=2 then avg(blocked)
                                                                 else sum(blocked) end as "Blocked",
        case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY in_progress))
             when (select * from median_average_or_cumulative)=2 then avg(in_progress)
                                                                 else sum(in_progress
                                                                 else sum(code_review) end as "InCode ProgressReview",
        case        case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY code_review))
             whenreview_and_testing))
             when (select * from median_average_or_cumulative)=2 then avg(codereview_and_review)
                                                                 else sum(code_review) end as "Code Review",
        case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY review_and_testing))
             when (select * from median_average_or_cumulative)=2 then avg(review_and_testing)
                                                                 elsetesting)
                                                                 else sum(review_and_testing) end as "Review and Testing"
from
(
(select select  name, start_date, workitem_id,
        case        case when status='To Do' then duration else null end as to_do,
        case
        case when status='Selected for Development' then duration else null end as selected_for_development,
        case
        case when status='Blocked' then duration else null end as blocked,
        case
        case when status='In Progress' then duration else null end as in_progress,
        case        case when status='Code Review' then duration else null end as code_review,
        case
        case when status='Review and Testing' then duration else null end as review_and_testing
from final_calculations
) as temp
group by name, start_date
order by start_date asc

...

Code Block

language	sql

select key, summary, type, priority, "Time in Status (d)" from
(
select key, summary, type, priority, round(duration::numeric,2) as "Time in Status (d)", name
from final_calculations
where status='To Do'
) as temp
where where name=clicked_x_value
order by "Time in Status (d)" desc

...

How to interpret this metric & next steps

Please read the information on how to interpret this metric here.

...

Version	Old Version 1	New Version Current
Changes made by	Aliaksei Apet	Aliaksei Apet
Saved on	Mar 30, 2023	Apr 26, 2023

Versions Compared

Key

Configuration

How to interpret this metric & next steps