Home  >  Q&A  >  body text

SQL query to get the time of each status

Given a table as shown below

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

Is there a way to track the time spent in each state based on the date the state changed? The table I'm working with only has a date column.

The desired result looks like this

|Time spent in phase|Status|
|2                  |Open
|7                  |In review
|5                  |Done

I only have one table to work with, so are there any queries that would solve this problem well? This workflow has rules set so that only open can go into progress.

If so, is it possible to use these workflow state changes to get underlying queries?

P粉605385621P粉605385621236 days ago353

reply all(1)I'll reply

  • P粉505917590

    P粉5059175902024-02-26 16:29:29

    DBFIDDLE

    Despite my last comment on your "question", I created a fiddle. It has two options:

    • Option 1
    SELECT
        DATEDIFF(COALESCE(LEAD(`Date`) OVER (ORDER BY `Date`),`date`),`Date`) as 'Time spent in phase',
        `From`
    FROM ThatLooksLikethis;

    Output:

    Time spent in stage from
    2 Open
    7 in progress
    5 under review
    0 Finish
    • Option 2:
    SELECT
        DATEDIFF(`Date`,COALESCE(LAG(`Date`) OVER (ORDER BY `Date`),`date`)) as 'Time spent in phase',
        `From`
    FROM ThatLooksLikethis;

    Output:

    Time spent in stage from
    0 Open
    2 in progress
    7 under review
    5 Finish

    P.S. This answer uses the MySQL 8.0 functions LEAD and LAG. If you are not using MySQL 8.0, but an older version, this answer may not be useful...

    reply
    0
  • Cancelreply