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?
1
0
0
Despite my last comment on your "question", I created a fiddle. It has two options:
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 |
SELECT
DATEDIFF(`Date`,COALESCE(LAG(`Date`) OVER (ORDER BY `Date`),`date`)) as 'Time spent in phase',
`From`
FROM ThatLooksLikethis;
Output:
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
