TSQL Calculate Time duration between blocks of events

Question

SQL Server 2008r2

Can you help me calculate the time duration between blocks of events?

Example dataset

Id  NodeId  StartTime           NextId
16  87771   2016-02-01 11:01:00 17
17  87771   2016-02-01 11:02:00 18
18  87771   2016-02-01 11:03:00 19
19  87771   2016-02-01 11:05:00 NULL

27  87774   2016-02-01 08:43:00 28
28  87774   2016-02-01 08:44:00 29
29  87774   2016-02-01 08:46:00 30
30  87774   2016-02-01 08:47:00 NULL

40  87771   2016-02-01 11:52:00 41
41  87771   2016-02-01 11:53:00 42
42  87771   2016-02-01 11:55:00 NULL

72  87774   2016-02-01 10:07:00 73
73  87774   2016-02-01 10:08:00 74
74  87774   2016-02-01 10:09:00 75
75  87774   2016-02-01 10:11:00 76
76  87774   2016-02-01 10:13:00 NULL

I'm after the time difference in seconds between IDs 16 - 19 and 27 - 30 and 40 - 42 etc

So expected result

NodeId  Duration_Seconds
87771   240
87774   240
87771   180
87774   360

Thanks in advance.

Answer 1

I ended up with something like this.

--using the sample data in #temp as kindly provided by Kamran
    SELECT
         C.NodeId
        ,SUM(DATEDIFF(SS, N.StartTime, C.StartTime ) )
    FROM
        #TEMP C
    CROSS APPLY
    (
        SELECT TOP 1
             NodeId
            ,StartTime
        FROM
            #TEMP M
        WHERE
            ID = C.ID - 1
    ) N
    WHERE
        N.NodeId = C.NodeId
    GROUP BY
            C.NodeId

Answer 2

Assuming that the blocks of events do not interleave with other blocks, then the solution is to identify each block by using a "window function" to make a running total of NULLS in the NextId column (from bottom to top).

Then you can easily find the min and max StartTime values in each block.

with blocks as (
    select *, 
        sum(case when NextId is null then 1 else 0 end) over (order by Id desc) as BlockId
    from #TEMP
)
select NodeId, DATEDIFF(second,min(StartTime), max(StartTime)) as Duration_Seconds 
from blocks
group by BlockId, NodeId
order by BlockId desc

I have used the setup which populates the #TEMP table copied from the answer by Kamran

Answer 3

another way of doing it is by recursion:

--using the sample data in #temp as kindly provided by Kamran
    ;with cte as
    (select id,nodeid, starttime, 0 as timediff, nextid from #temp where nextid is null
    union all
    select t.id, t.nodeid, t.starttime, datediff(second,t.starttime,c.starttime)+c.timediff timediff, t.nextid
    from cte c 
    join #temp t on t.nextid=c.id
    )
    select nodeid, timediff from cte c1
    where not exists (select 1 from cte c2 where c2.nextid=c1.id)
    order by id

(I'm assuming that there are no id gaps within blocks but there are id gaps between blocks)

Answer 4

Here it goes using a CURSOR yuk!!!

SAMPLE DATA:

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
    DROP TABLE #TEMP;

CREATE TABLE #TEMP(Id        INT,
                NodeId    INT,
                StartTime DATETIME,
                NextId    INT);

INSERT INTO #TEMP
VALUES
      (16, 87771, '2016-02-01 11:01:00', 17),
      (17, 87771, '2016-02-01 11:02:00', 18),
      (18, 87771, '2016-02-01 11:03:00', 19),
      (19, 87771, '2016-02-01 11:05:00', NULL),
      (27, 87774, '2016-02-01 08:43:00', 28),
      (28, 87774, '2016-02-01 08:44:00', 29),
      (29, 87774, '2016-02-01 08:46:00', 30),
      (30, 87774, '2016-02-01 08:47:00', NULL),
      (40, 87771, '2016-02-01 11:52:00', 41),
      (41, 87771, '2016-02-01 11:53:00', 42),
      (42, 87771, '2016-02-01 11:55:00', NULL),
      (72, 87774, '2016-02-01 10:07:00', 73),
      (73, 87774, '2016-02-01 10:08:00', 74),
      (74, 87774, '2016-02-01 10:09:00', 75),
      (75, 87774, '2016-02-01 10:11:00', 76),
      (76, 87774, '2016-02-01 10:13:00', NULL);

QUERY:

-- pull the data into a staging table with an extra column to hold GroupID 


IF OBJECT_ID('tempdb..#TEMP2') IS NOT NULL
    DROP TABLE #TEMP2;

SELECT *,
      CAST(NULL AS INT) AS groupID
INTO #temp2
FROM   #TEMP;

-- Unfortunately I cant see any other way except using a cursor put the results into groups. I really hate using cursors but...


DECLARE @NodeId  INT,
       @id      INT,
       @nextid  INT,
       @groupID INT = 1;

DECLARE group_Cursor CURSOR
FOR SELECT NodeId,
         Id,
         NextId
    FROM   #TEMP
    ORDER BY Id;

OPEN group_Cursor;

FETCH NEXT FROM group_Cursor INTO @NodeId,
                            @id,
                            @nextid;

WHILE @@FETCH_STATUS = 0
    BEGIN
       SELECT @NodeId,
            @id,
            @nextid;
       UPDATE A
        SET  A.groupID = @groupID
       FROM #temp2 AS A
       WHERE  A.NodeId = @NodeId
            AND A.Id = @id;
       FETCH NEXT FROM group_Cursor INTO @NodeId,
                                  @id,
                                  @nextid;
       IF @nextid IS NULL
          SELECT @groupID+=1;
    END;

CLOSE group_Cursor;

DEALLOCATE group_Cursor;

-- this is to resolve a bug where it gives groupID an extra increment if nextid IS NULL not sure why that's happening    
UPDATE t
  SET  t.groupID = t.groupID - 1
FROM #temp2 t
WHERE  nextid IS NULL;

SELECT t1.NodeId,
      SUM(DATEDIFF(SECOND, t1.StartTime, t2.StartTime))
FROM   #TEMP2 AS t1
      INNER JOIN #TEMP2 AS t2 ON t1.NodeId = t2.NodeId
                            AND t1.NextId = t2.Id
GROUP BY t1.NodeId,
        t1.groupID;

RESULTS: