CODEWITHSUNDEEP
sqldatetimesql-server-2014
Andreas
Andreas

Reputation: 13

Timespan calculation

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.

Upvotes: 1

Views: 84

Answers (1)

Sander
Sander

Reputation: 4042

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

Upvotes: 1

Related Questions