MySQL Historical timeline

Asked Oct 24 '22 at 17:55

Active Oct 24 '22 at 18:20

Viewed 11 times

I Would like create a historical data within timestamp over MySQL:

Example status table:

ticket_id       status      created
1               created     11/11/2021
1               review      11/12/2021
1               completed   11/13/2021
2               created     11/11/2021
2               review      11/12/2021
2               completed   11/13/2021

and my expected result is:

ticket id   created     review      completed   latest_status
1           11/11/2021  11/12/2021  11/13/2021  completed
2           11/11/2021  11/12/2021  11/13/2021  completed

Already tried using case when and max or min, but some timestamp not appeared. Anyone have any suggestion for this?

Using pivot with max case I got the following:

ticket id   created     review      completed   latest_status
1                                   11/13/2021  completed
2                                   11/13/2021  completed

Thanks in advanced

edited Oct 24 '22 at 18:20

asked Oct 24 '22 at 17:55

da_root

I tried that before, but if the current status used (max) of the ticket is completed, created and review timestamp return empty :( – da_root Oct 24 '22 at 18:18
Solved, creating subquery – da_root Oct 25 '22 at 04:06

MySQL Historical timeline

0 Answers0