CODEWITHSUNDEEP
mysqlsql
uncle bob
uncle bob

Reputation: 680

Calculate time diff by row in GROUP BY

i have table tiket_event like this, it store all events happen by changing status on each ticket

ticket_id status gmt_update
T01 OPEN 1620000488
T01 IN_PROGRESS 1620000588
T02 OPEN 1620000499
T01 DONE 1620000688
T02 CLOSE 1620000599
T01 CLOSE 1620000788

So now by mysql query, i want to group by ticket_id and calculate the amount of time between each status change,

for example T01 is change status from OPEN to IN_PROGRESS in 100ms, and from OPEN to DONE in 200ms

The issue is that few statuses will be missing in some cases, like T02, it has only OPEN and CLOSE directly.

Could anybody suggest query for this case?

EDIT: Result i want, i can update query for timediff of any pair of statuses

ticket_id open_to_inprogress_timediff open_to_done_timediff open_to_close_timediff done_to_close_timediff
T01 100 200 300 100
T02 0 0 100 0

Upvotes: 0

Views: 77

Answers (1)

Andreas
Andreas

Reputation: 159260

You would have to hard-code which differences to look for.

SELECT ticket_id
     , MAX(CASE status WHEN 'IN_PROGRESS' THEN gmt_update END) -
       MIN(CASE status WHEN 'OPEN' THEN gmt_update END)
       AS open_to_inprogress_timediff
     , MAX(CASE status WHEN 'DONE' THEN gmt_update END) -
       MIN(CASE status WHEN 'OPEN' THEN gmt_update END)
       AS open_to_done_timediff
     , MAX(CASE status WHEN 'CLOSE' THEN gmt_update END) -
       MIN(CASE status WHEN 'OPEN' THEN gmt_update END)
       AS open_to_close_timediff
     , MAX(CASE status WHEN 'CLOSE' THEN gmt_update END) -
       MIN(CASE status WHEN 'DONE' THEN gmt_update END)
       AS done_to_close_timediff
  FROM my_table
 GROUP BY ticket_id
 ORDER BY ticket_id

Upvotes: 1

Related Questions