with number_of_sprints_to_show as
(
select select 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
(
select select * from sprint
wherewhere start_date<now()
orderorder by start_date desc
limitlimit (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
from from scope a
leftleft 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
(
selectselect a.workitem_id, a.start, a.status, a.history_id, b.history_id as last_status,
casecase 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 select
a.id as workitem_id, a.key, a.summary, a.type, a.priority,
coalesce(b
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 from scope a
leftleft 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 finish_<start then coalesce(complete_date,now()) else finish_ end as finish --case when there was a re-asign from a sprint before sprint start date
from
(
select
a start, finish
from
(
select
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 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 afrom 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
), final_calculationswhere asfinish>start --joiningcase sprintwhen datesthere &was statuses 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 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 (b 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_PART
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 selectedin_for_developmentprogress))
when
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(blockedin_progress)
else sum(in_progress) end as "BlockedIn Progress",
case
case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY incode_progressreview))
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)=12 then avg(percentile_cont(0.5) WITHIN GROUP (ORDER BY code_review))
when (select * from median_average_or_cumulative)=2 then avg(code_review)
elsecode_review)
else sum(code_review) end as "Code Review",
case case when (select * from median_average_or_cumulative)=1 then (percentile_cont(0.5) WITHIN GROUP (ORDER BY review_and_testing))
when
when (select * from median_average_or_cumulative)=2 then avg(review_and_testing)
else
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 |