SQL to find timespan between rows based on ID

Question

I have the following table in a SQL db (HeartbeatHistory)

Timestamp | Comment | Id
------------------------

The comment can contain OK or ERR

The Id is the Id of the thing that has that comment.

I want to be able to query the table and find the durations that any given id was in an Error state.

Timestamp | Comment | Id
------------------------
12:00:00  | OK      | 1
11:59:00  | ERR     | 2
11:58:00  | OK      | 4
11:57:00  | OK      | 3
11:45:00  | ERR     | 4
11:20:00  | OK      | 2
11:00:00  | ERR     | 3
11:30:00  | OK      | 5
11:20:00  | ERR     | 1
11:10:00  | OK      | 1
11:00:00  | ERR     | 1
10:30:00  | ERR     | 5

So in the above table If I queried for 11:00:00 to 13:00:00 I would want to see.

ErrorStart | ErrorEnd | Id
--------------------------
11:00:00   | 11:10:00 | 1
11:20:00   | 12:00:00 | 1
11:59:00   | 12:00:00 | 2
11:00:00   | 11:57:00 | 3
11:45:00   | 11:58:00 | 4
11:00:00   | 11:30:00 | 5

(notice 5 started error before query date!!)

Is this possible? Also an Id might change state multiple times during the queried period.

So far I have this, which works for a single Id, but I need to make it work for multiple Ids.

declare @startDate datetime = @from;
declare @endDate datetime = @to;
declare @kpiId = 1;

select Foo.RowCreatedTimestamp, Foo.Comment, Foo.NextTimeStamp, Foo.NextComment, Foo.HeartBeatId, Foo.NextHeartBeatId
from (
    select RowCreatedTimestamp, Comment,
    lag(RowCreatedTimestamp, 1, 0) over (order by RowCreatedTimestamp desc) as NextTimeStamp, 
    lag(Comment, 1, 0) over (order by RowCreatedTimestamp desc) as NextComment,
    HeartBeatId
    from dbo.tblHeartbeatHistory
    where RowCreatedTimestamp >= @startDate and RowCreatedTimestamp <= @endDate
    and HeartbeatId in
        (
            select HeartbeatId
            from dbo.tblKpiHeartBeats
            where KpiId = @kpiId
        )                       
) as Foo
where Foo.Comment like '%set to ERR%'
order by Foo.RowCreatedTimestamp desc;

So if the select HeartbeatId from dbo.tblKpiHeartBeats returns a single Id, this works. As soon as their are multiple id's it does not :(

To avoid confusion:

The table with the Timestamp, Comment and Id is HeartbeatHistory.

The other table referenced in my SQL is dbo.tblKpiHeartBeats.

This table looks like:

Kpi | HeartbeatId
-----------------
1   | 1
1   | 2
1   | 3
1   | 4
1   | 5

So i want all the error intervals for Kpi = 1, it would return the error intervals for HeartbeatId 1,2,3,4 and 5.

Further note. The data may have multiple errors in a row before an OK comes in.

It may just be all ERR for the query period or all OK.

Answer 1

Try this:

DECLARE @table      TABLE (Timestmp TIME(1), Comment NVARCHAR(5), Id INT) --your table
INSERT INTO @table VALUES
('12:00:00','OK ','1'),('11:59:00','ERR','2'),('11:58:00','OK ','4'),('11:57:00','OK ','3'),
('11:45:00','ERR','4'),('11:20:00','OK ','2'),('11:00:00','ERR','3'),('11:30:00','OK ','5'),
('11:20:00','ERR','1'),('11:10:00','OK ','1'),('11:00:00','ERR','1'),('10:30:00','ERR','5')

DECLARE @ROWER TABLE (id INT IDENTITY(1,1), Timestmp TIME(1)) 
INSERT INTO @ROWER SELECT Timestmp FROM @table WHERE Comment='OK' ORDER BY Timestmp

DECLARE @TIME TIME(1) = '11:00:00' --your condition

SELECT DISTINCT CASE WHEN A.Timestmp >=@TIME THEN A.Timestmp ELSE @TIME END     ErrorStart,
        CASE WHEN B.Timestmp > A.Timestmp THEN B.Timestmp ELSE '' END   ErrorEnd,
A.Id  FROM (
SELECT ROW_NUMBER() OVER (ORDER BY id,Timestmp) rowid,* FROM @table WHERE Comment = 'ERR'
) A  LEFT JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY id,Timestmp) rowid,* FROM @table WHERE Comment = 'OK'
) B ON A.rowid = B.rowid
LEFT JOIN ( SELECT A.id,A.Timestmp t1,B.Timestmp t2 FROM @ROWER A 
            LEFT JOIN (SELECT id-1 id, Timestmp FROM @ROWER) B ON A.id=B.id
) C ON A.Timestmp BETWEEN C.t1 AND C.t2 ORDER BY A.Id

Hope it helps. :)

Answer 2

You can add second CTE Id you want full join ERR AND OK rows (Code below only for OK rows)

WIRH History AS (
    SELECT 
    FROM HeartbeatHistory  
    WHERE Timestamp BETWEEN @DateStart AND @DateEnd
), Errors AS(
    SELECT Id, MIN(Timestamp) AS ErrorStart 
    FROM History 
    WHERE Comment = 'ERR'
    GROUP BY Id
)
SELECT 
    ErrorStart = E.ErrorStart ,  
    ErrorEnd   = O.Timestamp,
    Id         = O.Id
FROM History           O
LEFT JOIN Errors       E ON E.Id = O.Id
WHERE O.Comment = 'OK'

Edit: You can add prevOK timespan (or PK) column to the table (probably computed persistent) - link to last good row. It will be used as Id of row in your report.

Try this index:

CREATE INDEX IDX_EXAMPLE ON HeartbeatHistory (Timestamp, Id, prevOK, Comment)

WIRH History AS (
    SELECT 
    FROM HeartbeatHistory  
    WHERE Timestamp BETWEEN @DateStart AND @DateEnd
)
SELECT 
    ErrorStart = E.ErrorStart ,  
    ErrorEnd   = O.Timestamp,
    Id         = O.Id
FROM History           O
OUTER APPLY (
    SELECT MIN(Timestamp) AS ErrorStart 
    FROM History  E
    WHERE E.Id = O.ID AND E.prevOK = O.prevOK 
)
WHERE O.Comment = 'OK'

Answer 3

The simplest method is to use lead(). If I assume that ERR does not occur twice in a row (as in your sample data):

select (case when timestamp >= '11:00:00' then timestamp else '11:00:00' end) as errorStart,
       (case when next_timestamp <= '13:00:00' then next_timestamp else '13:00:00') as errorEnd,
       id
from (select t.*,
             lead(timestamp) over (partition by id order by timestamp) as next_timestamp
      from t
     ) t
where comment = 'ERR' and
      (timestamp <= '13:00:00' and
       (next_timestamp >= '11:00:00' or next_timestamp is null)
      );