Time in Status by Sprint
- Aliaksei Apet
Context
Standard Time in Status metric works 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.
Description
This metric is based on Task Tracking System's (Jira, Azure Boards or Rally) data.
Tickets assigned to sprints are taken into account.
Metrics don'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.
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.
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.
Use logic 1 if you want to see what happened before assignment to a sprint - e.g. it could reveal carry-overs
Use logic 2 if you want to see where your ticket spends more time within 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 cumulative way.
Time in status is measured in days.
Configuration
We have 6 variables in the code of this metric:
Number of Sprints to Show. Number of latest sprints (including active one) to show on the metric. Default: 6
Done Statuses. The list of statuses considered completed. Default: Done, Closed.
Median, Average or Cumulative.
1 = Median time shows a time spent in particular status for a ticket in the middle of a data set - it means that half of the tickets has higher time in status, half of ticket - lower. Median is considered better than average if the data set is known to have some extreme values (high or low).
2 = Average time in status = sum up T status for all issues within the set period and divide by the number of issues.
3 = Cumulative time in status = sum up T status for all issues within the set period.
Calculation Logic.
1 = Metric takes into account the whole time ticket spent in a status (time ticket spent in it within sprint + all the time in this status before that). That's a default.
2 = Metric takes into account only time ticket spent in a status within particular sprint
Scope. Scope for tracking. Default: All tickets.
Statuses. In the final select query you would need to change the statuses according to your workflow.
Code
with number_of_sprints_to_show as
(
select 6 --how many sprints to show on the chart
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
(
select * from sprint
where start_date<now()
order by start_date desc
limit (select show from number_of_sprints_to_show)
),
status_history as --history of statuses
(
select a.id as workitem_id,
coalesce(b.start,a.created) as start,
coalesce(b.status,a.status) as status,
ROW_NUMBER() OVER (PARTITION BY workitem_id ORDER BY start asc) as history_id
from scope a
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 workitem_id,
max(history_id) as history_id
from status_history
group by workitem_id
),
status_history_with_last as --enrich status_history table with last status info
(
select a.workitem_id, a.start, a.status, a.history_id, b.history_id as last_status,
case when b.history_id is not null then a.start else null end as last_status_date
from status_history a
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.id as workitem_id, a.key, a.summary, a.type, a.priority,
coalesce(b.start,a.created) as start,
coalesce(b.sprint,a.sprints::text) as sprint,
ROW_NUMBER() OVER (PARTITION BY workitem_id ORDER BY start asc) as history_id
from scope a
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_id, key, summary, type, priority,
id, name, start_date, complete_date,
start, finish
from
(
select
a.workitem_id, a.key, a.summary, a.type, a.priority,
s.id, s.name, s.start_date, s.complete_date,
greatest(max(b.start),s.start_date) as start,
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||'%'
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
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.summary, a.type, a.priority,
a.id, a.name, a.start_date,
b.status,
sum(case when (b.history_id=c.last_status) and (select * from calculation_logic)=1 then --status=current status + logic=1
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 (b.history_id=c.last_status) and (select * from calculation_logic)=2 then --status=current status + logic=2
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 (b.history_id!=c.last_status) and (select * from calculation_logic)=1 then --status!=current status + logic=1
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 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 code_review))
when (select * from median_average_or_cumulative)=2 then avg(code_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)
else sum(review_and_testing) end as "Review and Testing"
from
(
select name, start_date, workitem_id,
case when status='To Do' then duration else null end as to_do,
case when status='Selected for Development' then duration else null end as selected_for_development,
case when status='Blocked' then duration else null end as blocked,
case when status='In Progress' then duration else null end as in_progress,
case when status='Code Review' then duration else null end as code_review,
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 |
Drill-down
Drill-down query to show the ticket's attributes & results of calculation for a clicked sprint:
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 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.