Tracking time in each status per ticket? (Jira/MySQL)
Solution 1:
something along the lines of : Sample data
create table ticket (
dated date,
from_state varchar(10),
to_state varchar(10),
ticket_id int
);
insert into ticket
values( '2021-01-01', 'open', 'prog', 1);
insert into ticket
values( '2021-01-04', 'prog', 'rev', 1);
insert into ticket
values( '2021-01-07', 'rev', 'done', 1);
insert into ticket
values( '2021-01-03', 'open', 'prog', 2);
insert into ticket
values( '2021-01-04', 'prog', 'rev', 2);
insert into ticket
values( '2021-01-04', 'rev', 'prog', 2);
insert into ticket
values( '2021-01-10', 'prog', 'done', 2);
query:
with partitioned as (
select
row_number() over(partition by ticket_id order by dated) rn,
ticket_id, from_state state, dated start_date
from ticket
),
date_range as (
select
start_state.*,
end_state.start_date as end_date
from partitioned start_state left join partitioned end_state on
start_state.ticket_id = end_state.ticket_id and start_state.rn = end_state.rn -1
),
days_in_state as (
select ticket_id, state, datediff(end_date, start_date) as days
from date_range
)
select ticket_id, state, sum(days) days_in_state
from days_in_state
group by 1,2
order by 1,2
The first query part 'partitioned' uses a windowing function to group the data for each ticket.
The second part uses a self join link each state for a ticket to the next state for the same ticket (using the row number from the first step)
the third part calculates the number of days in each state (if a ticket transitions to the same state multiple times it will have multiple rows for that stat)
finally calculate the totals in each state for the ticket
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html