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?
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