with sprints as ( select id, name, coalesce(activated_date, start_date) + (sprint_starting_grace_period())::INTERVAL as start_date, coalesce(complete_date, finish_date) as finish_date from sprint where state != 'FUTURE' and start_date is not null and start_date < now() and (finish_date is not null or complete_date is not null) order by finish_date desc limit 6 ), scope as ( select * from ticket where parent_task is null ), time_estimate_field_update_history as ( select t.id as workitem_id, coalesce(h.start,t.created) as updated_date, -- if there is no time_estimate updates in ticket history then get time_estimate from ticket (case when h.start is null then t.time_estimate else h.time_estimate end) as time_estimate from scope t left join tickethistory h on t.id = h.workitem_id and h.field = 4 ), time_estimate_update_history_on_sprint_start as ( select distinct on (h.workitem_id, s.id) h.workitem_id, s.id as sprint_id, h.time_estimate from time_estimate_field_update_history h, sprints s where h.updated_date < s.start_date order by h.workitem_id, s.id, h.updated_date desc ), time_estimate_update_history_on_sprint_finish as ( select distinct on (h.workitem_id, s.id) h.workitem_id, s.id as sprint_id, h.time_estimate from time_estimate_field_update_history h, sprints s where h.updated_date < s.finish_date order by h.workitem_id, s.id, h.updated_date desc ), sprint_field_update_history as ( select t.id as workitem_id, coalesce(h.start,t.created) as updated_date, -- if there is no sprint updates in ticket history then get sprints from ticket (case when h.start is null then t.sprints else string_to_array(h.sprint, ',')::bigint[] end) as sprint from scope t left join tickethistory h on t.id=h.workitem_id and h.field = 1 ), sprint_update_history_on_sprint_start as ( select distinct on (h.workitem_id, s.id) h.workitem_id, s.id, h.sprint from sprint_field_update_history h, sprints s where h.updated_date < s.start_date order by h.workitem_id, s.id, h.updated_date desc ), sprint_update_history_on_sprint_finish as ( select distinct on (h.workitem_id, s.id) h.workitem_id, s.id, h.sprint from sprint_field_update_history h, sprints s where h.updated_date < s.finish_date order by h.workitem_id, s.id, h.updated_date desc ), start_sprint_scope as ( select distinct s.id as sprint_id, array_agg(workitem_id) as tickets from sprints s inner join sprint_update_history_on_sprint_start h on h.id = s.id and s.id = any(h.sprint) group by sprint_id ), finish_sprint_scope as ( select s.id as sprint_id, array_agg(workitem_id) as tickets from sprints s inner join sprint_update_history_on_sprint_finish h on h.id = s.id and s.id = any(h.sprint) group by sprint_id ), sprint_stat as ( select s.id, s.name, s.start_date, coalesce(sc.tickets, array[]::bigint[]) as start_items, coalesce(fc.tickets, array[]::bigint[]) as finish_items, array_subtract(coalesce(fc.tickets, array[]::bigint[]), coalesce(sc.tickets, array[]::bigint[])) as added_items from sprints s left join start_sprint_scope as sc on s.id = sc.sprint_id left join finish_sprint_scope as fc on s.id = fc.sprint_id ), sprint_sp_stat as ( select s.name, s.start_date, (select coalesce(sum(time_estimate), 0) from time_estimate_update_history_on_sprint_start h where h.sprint_id = s.id and h.workitem_id = any(start_items) ) as start_hours, (select coalesce(sum(time_estimate), 0) from time_estimate_update_history_on_sprint_finish h where h.sprint_id = s.id and h.workitem_id = any(added_items) ) as added_hours from sprint_stat s ) select name as "Sprint", (case when added_hours = 0 then 0 when start_hours = 0 then 100.00 else round(added_hours::numeric / start_hours ::numeric * 100, 2) end) as "Sprint scope creep" from sprint_sp_stat order by start_date
----DRILL DOWN----
with sprint_added_items as ( select s.name, unnest(s.added_items) as ticket_id from sprint_stat s ) select (case when t.url is not null then '['||t.key||']('||t.url||')' else t.key end) as "Issue Id", t.type as "Type", t.priority as "Priority", t.summary as "Summary" from sprint_added_items s inner join ticket t on s.ticket_id = t.id where name = clicked_x_value order by (regexp_matches(t.key,'\d+'))[1]::bigint
