Hi everyone,
I have a question about how to calculate time difference between two rows depending on the status. Here I provided an example:
ID Date Status
A1 23.11.2017 Initial
A2 23.11.2017 Initial
B1 24.11.2017 Initial
A1 26.11.2017 testing
A2 28.11.2017 blocked
B1 28.11.2017 testing
A1 02.12.2017 completed
A2 03.12.2017 blocked
B1 05.12.2017 blocked
A2 13.12.2017 completed
B1 13.12.2017 completed
Now I want to create a column, where the value is the duration from "Status=Initial" to "Status=completed" for the specific ID. In this example, Duration for A1, A2 and B1 are respectively, 9, 20, 19.
How can I write the sql script about the idea like this?
Thanks!