How to get the diff of dates of different rows, based on some common values on each row?

Question

maybe somebody can help starting to solve this topic. I have a table with >20k rows with the following columns, it is a log table that manages the information of changes on log lvl:

Looking at a way to calculate the cycle time of a task, I would like to know how much time passes between the change of a status (field = "status").

A task can have several status changes, so I cannot pretend that I have a start and an end status and just filter those.

Everytime a status changes, I want to know how much time passed in comparison with the last status change.

I am working on mysql. Any idea how I can start? If you need more info, let me know.

Thx!

EDIT:

Here the current table structure:

Select P_ID,P_KEY,AUTHOR,Created,fieldJ,fromString,toString from HISTORY where fieldJ = "status";

RESULT:
P_ID;P_KEY;AUTHOR;Created;fieldJ;fromString;toString
P_ID;P_KEY;AUTHOR;Created;fieldJ;fromString;toString
10865;JIRA-176;Former user;2021-02-09 08:15:38;status;To Do;In Progress
10865;JIRA-176;Former user;2021-02-19 09:44:32;status;In Progress;In Test
10865;JIRA-176;Former user;2021-02-19 10:08:56;status;In Test;In Test
10865;JIRA-176;Former user;2021-03-26 09:54:15;status;In Test;Done
14223;JIRA-900;Former user;2023-02-02 19:41:46;status;To Do;IN REVIEW.
14223;JIRA-900;Former user;2023-02-07 20:19:12;status;IN REVIEW.;Refined
14223;JIRA-900;Former user;2023-02-09 09:58:02;status;Refined;To Do
14223;JIRA-900;Former user;2023-02-15 14:18:34;status;To Do;In Progress
14223;JIRA-900;Former user;2023-02-16 08:12:33;status;In Progress;Ready for QC
14223;JIRA-900;Former user;2023-02-16 13:29:03;status;Ready for QC;In Testing
14223;JIRA-900;Former user;2023-02-20 06:50:31;status;In Testing;Ready for UAT
14223;JIRA-900;Former user;2023-02-20 10:18:12;status;Ready for UAT;In UAT
14223;JIRA-900;Former user;2023-02-21 18:50:55;status;In UAT;Pre-Testing
14223;JIRA-900;Former user;2023-02-21 18:51:08;status;Pre-Testing;UAT In Test
14223;JIRA-900;Former user;2023-02-27 11:47:17;status;UAT In Test;Feasibility
14223;JIRA-900;Former user;2023-03-29 14:48:06;status;Feasibility;Ready for PROD
14223;JIRA-900;Former user;2023-03-29 16:59:45;status;Ready for PROD;IN PROD

** "Formal user" is a placeholder for one or several users

Expected Outcome:

TABLE

P_KEY    | status         | TIMEDIFF
JIRA-176 | In Progress    | 241:28:54 
JIRA-176 | In Test        | 00:24:24
JIRA-176 | In Test        | ...

What I tried so far:


SELECT h.ID, h.P_KEY,h.fieldJ, h.created, h.fromString, h.toString, h2.created, h2.fromString, TIMEDIFF(h2.created,h.created) AS "TD"

FROM HISTORY AS h
LEFT JOIN HISTORY AS h2 ON h.toString = h2.fromString AND h2.created > h.created AND h.P_key = h2.P_key AND h2.fieldJ = "status" 
WHERE h.fieldJ = "status" 
ORDER BY h.created asc

WRONG OUTPUT:
ID;P_KEY;fieldJ;created;fromString;toString;created;fromString;TD
19133;JIRA-176;status;2021-02-09 08:15:38;To Do;In Progress;2021-02-19 09:44:32;In Progress;241:28:54
19221;JIRA-176;status;2021-02-19 09:44:32;In Progress;In Test;2021-03-26 09:54:15;In Test;838:59:59
19221;JIRA-176;status;2021-02-19 09:44:32;In Progress;In Test;2021-02-19 10:08:56;In Test;00:24:24
19224;JIRA-176;status;2021-02-19 10:08:56;In Test;In Test;2021-03-26 09:54:15;In Test;838:59:59

EDIT 2:

I found the solution to my problem, by following this Link, with the same problem, after I understood how the data could be managed. Here my final query:

SELECT 
AllData.ID, AllData.K, AllData.`Status`,  AllData.TIME1, AllData.TIME2, TIMEDIFF(AllData.TIME2,AllData.TIME1)
FROM
(
  SELECT 
    h1.P_Id AS 'ID', h1.P_key AS 'K', h1.created AS 'TIME1', 
    h1.toString AS 'Status',
    (
      SELECT h2.created FROM HISTORY AS h2 
      WHERE h2.created > h1.created 
      AND h2.fieldJ = "status" 
      AND h2.P_key = h1.P_Key LIMIT 1
    ) AS 'TIME2'
  FROM HISTORY AS h1
  WHERE fieldJ = "status"
  ) AS AllData 
ORDER BY ID

Could you please include sample data and your expected output. Also would be great if you can show what you have tried so far :) — DataWrangler, Apr 26 '23 at 16:00
Please would you read, [Tips for asking a good Structured Query Language (SQL) question)](https://meta.stackoverflow.com/questions/271055) and amend your question accordingly. — RiggsFolly, Apr 26 '23 at 16:42
OK, I think I found the solution to my problem, pls see the post. — Christian, Apr 28 '23 at 15:06

score 0 · Answer 1 · answered May 03 '23 at 14:21

I found the solution to my problem, by following this Link, with the same problem, after I understood how the data could be managed. Here my final query:

SELECT 
AllData.ID, AllData.K, AllData.`Status`,  AllData.TIME1, AllData.TIME2, TIMEDIFF(AllData.TIME2,AllData.TIME1)
FROM
(
  SELECT 
    h1.P_Id AS 'ID', h1.P_key AS 'K', h1.created AS 'TIME1', 
    h1.toString AS 'Status',
    (
      SELECT h2.created FROM HISTORY AS h2 
      WHERE h2.created > h1.created 
      AND h2.fieldJ = "status" 
      AND h2.P_key = h1.P_Key LIMIT 1
    ) AS 'TIME2'
  FROM HISTORY AS h1
  WHERE fieldJ = "status"
  ) AS AllData 
ORDER BY ID

How to get the diff of dates of different rows, based on some common values on each row?

1 Answers1