I have a table like this:
#Row ID Status1 Status2 TimeStatusChange
------------------------------------------
1 24 0 0 2020-09-02 09:18:02.233
2 48 0 0 2020-09-02 09:18:58.540
3 24 1 0 2020-09-02 09:19:47.233
4 24 0 0 2020-09-02 09:19:47.587
5 48 0 1 2020-09-02 09:22:53.923
6 36 1 0 2020-09-02 09:24:14.343
7 48 0 0 2020-09-02 09:24:49.670
8 24 1 0 2020-09-02 09:38:37.820
and would like to know, how to calculate the sum of timespans for all status (1 or 2) changes from 0 to 1 (or 1 to 0) grouped by ID.
In this example for ID 24, Status1 from 0 to 1, it would be the difference of TimeStatusChange of #Row 3 and #row 1 + difference of TimeStatusChange of #Row 8 and #row 4, roughly 21 minutes.
The perfect output would look like this:
ID Change TimeSpanInMinutes
----------------------------------------
24 Status1_from_0_1 20
36 .....
Although I have some experience with PL/SQL, I am not getting anywhere.
Sample data
I added a couple rows to have some more result data and validate the scenario where there are successive rows with the same status for a given ID.
declare @data table
(
ID int,
Status1 int,
Stamp datetime
)
insert into @data (ID, Status1, Stamp) values
(48, 1, '2020-09-02 09:00:00.000'), --added row
(24, 0, '2020-09-02 09:18:02.233'),
(48, 0, '2020-09-02 09:18:58.540'),
(24, 1, '2020-09-02 09:19:47.233'),
(24, 0, '2020-09-02 09:19:47.587'),
(48, 0, '2020-09-02 09:22:53.923'),
(36, 1, '2020-09-02 09:24:14.343'),
(48, 0, '2020-09-02 09:24:49.670'),
(24, 1, '2020-09-02 09:38:37.820'),
(48, 1, '2020-09-02 10:00:00.000'); --added row
Solution
Uses a common table expression (CTE, cte_data) to fetch the previous record for the same ID (regardless of its status value) with the help of the lag() function. Succeeding rows with the same value as the previous row are removed in the where clause outside the CTE.
with cte_data as
(
select d.ID,
d.Status1,
d.Stamp,
lag(d.Status1) over(partition by d.ID order by d.Stamp) as Status1Prev,
lag(d.Stamp) over(partition by d.ID order by d.Stamp) as StampPrev
from @data d
)
select d.ID,
d.Status1Prev as Status1From,
d.Status1 as Status1To,
sum(datediff(MI, d.StampPrev, d.Stamp)) as StampDiffSumM, --minutes
convert(time(3), dateadd(MS, sum(datediff(MS, d.StampPrev, d.Stamp)), '1900-01-01 00:00:00.000')) as StampDiffSumF --formatted
from cte_data d
where d.Status1 <> d.Status1Prev
and d.Status1Prev is not null
group by d.ID, d.Status1Prev, d.Status1
order by d.ID;
Result
ID Status1From Status1To StampDiffSumM StampDiffSumF
----------- ----------- ----------- ------------- ----------------
24 0 1 20 00:20:35.233
24 1 0 0 00:00:00.353
48 0 1 36 00:35:10.330
48 1 0 18 00:18:58.540