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