SQL query to get the time of each status

Question

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?

Answer 1

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