Tracking time in each status per ticket? (Jira/MySQL)

Question

So I currently am using Jira with Domo to create Dashboards for our team.

Currently I am wanting to display the time each ticket spends in each status per ticket.

For example I have a table like this.

 
  |date      |From       | To                 |Ticket ID
  |01/01/21  |Open       |In progress         |1
  |01/03/21  |In progress|In review           |1
  |01/10/21  |In Review  |Done                |1
  |01/15/21  |Done       |Resolved            |1
  |01/01/21  |Open       |In progress         |2
  |01/03/21  |In progress|In review           |2
  |01/10/21  |In Review  |Done                |2
  |01/15/21  |Done       |Resolved            |2

With this I would like to be able to see how long each ticket spends in each status.

A single ticket can be go back to a status multiple times before it is done.

I really have no idea where to start with a mysql query to start with this. Is there a simple query to do this?

Answer 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