with field_number_mapping as (
select 'status' as field, (select field from TicketHistory where status is not null and length(status) > 0 limit 1) as num
union select 'story_points' as field, (select field from TicketHistory where story_points is not null limit 1) as num
union select 'sprint' as field, (select field from TicketHistory where sprint is not null and length(sprint) > 0 limit 1) as num
),
status_field as (
select num from field_number_mapping where field = 'status' limit 1
),
sprint_field as (
select num from field_number_mapping where field = 'sprint' limit 1
),
story_points_field as (
select num from field_number_mapping where field = 'story_points' limit 1
),
closed_statuses as (
select a
from (
values ('Closed'), ('Verified')
) s(a)
),
last_sprint as ( -- get the last started sprint
select * from Sprint where start_date is not null and state =in ('ACTIVE', 'CLOSED') order by start_date desc limit 1
),
last_sprint_like as (
select concat('%', (select id from last_sprint), '%')
),
affected_tickets as ( -- get all tickets somehow related to this sprint
select distinct(t.*)
from Ticket t
join TicketHistory th on th.workitem_id = t.id
where th.sprint like concat('%', (select id from last_sprint),'%') -- Ticket was ever added to sprint
or (select id from last_sprint) = any(t.sprints) -- Ticket is in sprint right now (includint the case when it was added to sprint on ticket creation, which is not usually mentioned in history)
),
affected_history as (
-- get all history items related to the tickets related to sprint
select th.workitem_id, th.start, th.field, th.status, th.sprint, th.story_points, th.start = t.created as on_creation
from affected_tickets t join TicketHistory th on th.workitem_id = t.id and th.field in (select num from field_number_mapping)
union select t.id as workitem_id, t.created as start, (table status_field) as field, t.status, null as sprint, null as story_points, true as on_creation
from affected_tickets t left join TicketHistory th on th.workitem_id = t.id and th.field = (table status_field) where th.start is null
-- ^^ to substitute lack of history on ticket creation for status
union select t.id as workitem_id, t.created as start, (table sprint_field) as field, null as status, array_to_string(t.sprints, ', ') as sprint, null as story_points, true as on_creation
from affected_tickets t left join TicketHistory th on th.workitem_id = t.id and th.field = (table sprint_field) where th.start is null
-- ^^ to substitute lack of history on ticket creation for sprint
union select t.id as workitem_id, t.created as start, (table story_points_field) as field, null as status, null as sprint, t.story_points, true as on_creation
from affected_tickets t left join TicketHistory th on th.workitem_id = t.id and th.field = (table story_points_field) where th.start is null
-- ^^ to substitute lack of history on ticket creation for story_points
),
changes_dates as (
select distinct workitem_id, start, on_creation from affected_history
-- ^^ will need this further for grouping history entries by time (several changes may have been done at the same time but stored as different history items)
),
became_extraction as (
-- moving to format was -> became for 3 fields in focus. This is the 'became' part
select *,
(case when exists(select ah.sprint from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and ah.field = (table sprint_field))
then coalesce((select ah.sprint from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and ah.field = (table sprint_field) limit 1), 'No Sprint')
else null end) as became_sprint, -- if there are no history items where sprint was changed at this moment, there'll be null, otherwise the value of that 'same moment' sprint change
(case when exists (select ah.status from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and ah.field = (table status_field))
then coalesce ((select ah.status from affected_history ah where ah.field = (table status_field) and ah.start = cd.start and ah.workitem_id = cd.workitem_id limit 1), 'No Status')
else null end) -- if there are no history items where status was changed at this moment, there'll be null, otherwise the value of that 'same moment' status change
as became_status,
(case when exists(select ah.story_points::text from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and field = (table story_points_field))
then coalesce((select ah.story_points::text from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and field = (table story_points_field)limit 1), 'No SPs')
else null end) -- if there are no history items where story_points was changed at this moment, there'll be null, otherwise the value of that 'same moment' story_points change
as became_story_points
from changes_dates cd
),
was_extraction as (
-- this is the 'was' part for corresponding 'became' entries
select *,
(select coalesce(ah.sprint, 'No Sprint') from affected_history ah where field = (table sprint_field) and ah.start < cd.start and ah.workitem_id = cd.workitem_id order by ah.start desc limit 1) as was_sprint,
(select coalesce(ah.status, 'No Status') from affected_history ah where field = (table status_field) and ah.start < cd.start and ah.workitem_id = cd.workitem_id order by ah.start desc limit 1) as was_status,
(select coalesce(ah.story_points::text, 'No SPs') from affected_history ah where field = (table story_points_field) and ah.start < cd.start and ah.workitem_id = cd.workitem_id order by ah.start desc limit 1) as was_story_points
from changes_dates cd
where on_creation = false -- because if the change was done after creation, jira creates 2 history items - one for the creation moment, one for change (donno why)
),
history_extraction as (
-- here we combine 'was' and 'became' using join to get all the fields. select from this table to see results
select be.workitem_id, be.start, be.on_creation,
coalesce(we.was_sprint, 'No Sprint') as was_sprint, coalesce(be.became_sprint, we.was_sprint) as became_sprint,
coalesce(we.was_status, 'No Status') as was_status, coalesce(be.became_status, we.was_status) as became_status,
coalesce(we.was_story_points, 'No SPs') as was_story_points, coalesce(be.became_story_points, we.was_story_points) as became_story_points
from became_extraction be
left join was_extraction we
on be.workitem_id = we.workitem_id and be.start = we.start
),
meaningful_history_extraction as (
-- here we apply criterias to fields so that they make more sense to us when making decision on whether add or subtract story points from sprint scope based on what happened with the ticket at that moment
select he.workitem_id, he.start, he.on_creation,
he.was_sprint like (table last_sprint_like) as was_my_sprint,
he.became_sprint like (table last_sprint_like) as became_my_sprint,
he.was_status in (table closed_statuses) as was_closed,
he.became_status in (table closed_statuses) as became_closed,
(case when he.was_story_points = 'No SPs' or he.was_story_points is null then 0 else he.was_story_points::numeric end) as was_story_points,
(case when he.became_story_points = 'No SPs' or he.became_story_points is null then 0 else he.became_story_points::numeric end) as became_story_points
from history_extraction he
),
decision_map as (
-- here we set the rules by which we decide whether add or subtract story points from sprint scope based on what happened with the ticket at that moment
select *
from (
-- was_my_sprint, became_my_sprint, was_closed, became_closed, was_story_points_multiplier, became_story_points_multiplier
values (false, false, false, false, 0, 0),
(false, false, false, true, 0, 0),
(false, false, true, false, 0, 0),
(false, false, true, true, 0, 0),
(false, true, false, false, 0, 1),
(false, true, false, true, 0, 0),
(false, true, true, false, 0, 1),
(false, true, true, true, 0, 0),
(true, false, false, false, -1, 0),
(true, false, false, true, -1, 0),
(true, false, true, false, 0, 0),
(true, false, true, true, 0, 0),
(true, true, false, false, 0-1, 01),
(true, true, false, true, -1, 0),
(true, true, true, false, 0, 1),
(true, true, true, true, 0, 0)
) s(was_my_sprint, became_my_sprint, was_closed, became_closed, was_story_points_multiplier, became_story_points_multiplier)
),
operations as (
-- here we join what we got with the decision map to get actual additions and deletions
select he.*,
he.was_story_points * dm.was_story_points_multiplier as was_sp_operand,
he.became_story_points * dm.became_story_points_multiplier as became_sp_operand
from meaningful_history_extraction he
join decision_map dm on
he.was_my_sprint = dm.was_my_sprint
and he.became_my_sprint = dm.became_my_sprint
and he.was_closed = dm.was_closed
and he.became_closed = dm.became_closed
and (he.was_story_points <> 0 or he.became_story_points <> 0) -- always good to filter out what doesn't affect the scope
order by start
),
totaladditions as (
-- here we combine the operands based on was_sps and became_sps into one delta - the change of sprint scope
select was_sp_operand + became_sp_operand as delta, * from operations where was_sp_operand <> 0 or became_sp_operand <> 0 order by start -- always good to filter out what doesn't affect the scope
),
cumulative as (
-- here we sum up all changes to get the sprint scope size on each event
select sum(delta) over (order by start) as cumulative, * from totaladditions order by start
),
generated_sprint_border_series as (
select date_trunc('hour', dd) dd from generate_series((select start_date from last_sprint limit 1)::timestamp,
(select finish_date from last_sprint limit 1)::timestamp, '1 hour'::interval) dd
)
select to_char(dd, 'MM/dd HH:MI Dy') as "Time", (
select cumulative from cumulative where start < dd and dd < now()
order by start desc limit 1) as "Story Points"
from generated_sprint_border_series |