I have a table which contains various changes in work order states as in the following example
I would like to segregate the above rows in to time windows as "In-Progress" to "Completed". In our above example
rows 2&3 are as "In-Progress" and Row 1 "Completed"
So result set shall be
In-Progress from "12/11/2017 05:38:48" till completed on "05/01/2018 18:01:01"
I tried CTE, changing all not complete status as in-progress and finding the time difference. However am able to get it only for the first completed till in-progress.
any help to resolve this is appreciated. Thanks in advance.