How to get time ranges from a table with time-based digital values?

Question

I do have a (SQL Server) database table that contains an activity log of a device. The table consists of a DeviceId, a timestamp (DateTime) and a value field. The device writes its state changes to the DB whenever it turns on or off (value 1 resp. 0).

Now I wonder what would be the fastest way to get "blocks of activity" from that table. What do I mean by that? I'd like to get all time periods that are defined by a "1" value and its subsequent "0" value for a given DeviceId, so that I get a list of time ranges like this (for the active blocks, the inactive times would be between a 0 value followed by a 1):

DateTime ActiveStart, DateTime ActiveEnd

I currently ended up by first getting all the entries with EF as a list, then looping over them and comparing each entry to its predecessor in order to check if the device had been turned on and off.

That does work, but I do think that there must be a better and more performant way of doing this. What would be the best way to do it? Either a pure SQL query (from which I could build me a Stored Procedure) or a LINQ to SQL query will do.

Thanks for your thoughts and comments!

Answer 1

--------------------------
------ sample data -------
--------------------------
declare @t table 
(
    DeviceId int,
    Timestamp DateTime,
    Value bit
)

insert into @t values
(1, '2016-01-01', 1),
(1, '2016-01-05', 1),
(1, '2016-01-07', 1),
(1, '2016-01-08', 0),
(1, '2016-01-10', 0),
(1, '2016-01-21', 0),
(1, '2016-01-22', 1),
(1, '2016-01-25', 0),
(2, '2016-01-02', 1),
(2, '2016-01-04', 0),
(2, '2016-01-06', 1),
(2, '2016-01-08', 0),
(2, '2016-01-09', 1),
(2, '2016-01-15', 0),
(2, '2016-01-18', 1)

--------------------------
---------- query ---------
--------------------------

select
    DeviceId,
    gr,
    ActiveStart = max(case when Value = 1 then Timestamp end),
    ActiveEnd = max(case when Value = 0 then Timestamp end)
from
(
    select 
        *,
        gr = Value + row_number() over(partition by DeviceId order by Timestamp)
    from @t
) t
group by DeviceId, gr
-- optional sorting by dates for easier results evaluation:
--order by DeviceId,
--  case when max(case when value = 1 then Timestamp end) is NULL
--  then max(case when value = 0 then Timestamp end)
--  else max(case when value = 1 then Timestamp end) end

Answer 2

You might try it like this:

CREATE TABLE #deviceLog (DeviceID INT, Activity DATETIME,Stat INT);
INSERT INTO #deviceLog VALUES
 (1,{ts'2016-04-04 11:20:00'},1)
,(1,{ts'2016-04-04 11:30:00'},0)
,(1,{ts'2016-04-04 11:33:00'},1)
,(1,{ts'2016-04-04 11:38:00'},0)
,(2,{ts'2016-04-04 12:33:00'},1)
,(2,{ts'2016-04-04 12:40:00'},0)
,(3,{ts'2016-04-04 10:33:00'},1)
,(3,{ts'2016-04-04 11:38:00'},0);

WITH AllOn AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY DeviceID ORDER BY Activity) AS Inx,*
    FROM #deviceLog
    WHERE Stat=1
)
,AllOff AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY DeviceID ORDER BY Activity) AS Inx,*
    FROM #deviceLog
    WHERE Stat=0
)
SELECT AllOn.*,AllOff.Activity AS OffActivity
FROM AllOn
INNER JOIN AllOff ON AllOn.DeviceID=AllOff.DeviceID AND AllOn.Inx=AllOff.Inx;

DROP TABLE #deviceLog;

The result

Inx DeviceID    Activity          Stat  OffActivity
 1     1    2016-04-04 11:20:00.000 1   2016-04-04 11:30:00.000
 2     1    2016-04-04 11:33:00.000 1   2016-04-04 11:38:00.000
 1     2    2016-04-04 12:33:00.000 1   2016-04-04 12:40:00.000
 1     3    2016-04-04 10:33:00.000 1   2016-04-04 11:38:00.000

Answer 3

SQL Server 2012+ supports cumulative sums. You can get blocks of activity by counting the number of *0*s cumulatively. A block of activity will have a constant value. Then you can aggregate (and filter) to get the periods of activity:

select deviceid, min(timestamp), max(timestamp)
from (select t.*,
             sum(case when value = 0 then 1 else 0 end) over
                 (partition by deviceid order by timestamp) as grp
      from t
     ) t
where value <> 0
group by deviceid, grp;

In earlier versions of SQL Server, you can do something similar using outer apply (and by other methods).