By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.27 |
CREATE TABLE ThatLooksLikethis (`Date` date, `From` VARCHAR(40), `To` VARCHAR(40));
INSERT INTO ThatLooksLikethis VALUES
('2021-01-01', 'Open' , 'In progress'),
('2021-01-03', 'In progress','In review'),
('2021-01-10','In review','Done'),
('2021-01-15','Done','Resolved');
SELECT * FROM ThatLooksLikethis;
| Date | From | To |
|---|---|---|
| 2021-01-01 | Open | In progress |
| 2021-01-03 | In progress | In review |
| 2021-01-10 | In review | Done |
| 2021-01-15 | Done | Resolved |
-- First determine next date
SELECT
`Date`,
COALESCE(LEAD(`Date`) OVER (ORDER BY `Date`),`Date`) as NextDate,
`From`,
`To`
FROM ThatLooksLikethis;
| Date | NextDate | From | To |
|---|---|---|---|
| 2021-01-01 | 2021-01-03 | Open | In progress |
| 2021-01-03 | 2021-01-10 | In progress | In review |
| 2021-01-10 | 2021-01-15 | In review | Done |
| 2021-01-15 | 2021-01-15 | Done | Resolved |
-- Option1
SELECT
DATEDIFF(COALESCE(LEAD(`Date`) OVER (ORDER BY `Date`),`date`),`Date`) as 'Time spent in phase',
`From`
FROM ThatLooksLikethis;
| Time spent in phase | From |
|---|---|
| 2 | Open |
| 7 | In progress |
| 5 | In review |
| 0 | Done |
-- Option2
SELECT
DATEDIFF(`Date`,COALESCE(LAG(`Date`) OVER (ORDER BY `Date`),`date`)) as 'Time spent in phase',
`From`
FROM ThatLooksLikethis;
| Time spent in phase | From |
|---|---|
| 0 | Open |
| 2 | In progress |
| 7 | In review |
| 5 | Done |