D. Caan D. Caan - 1 month ago 5
SQL Question

SQL Total time based on Timestamp and States

I have a table like this:

Timestamp | State
01-jan-2016 00:01:00 | ON
01-jan-2016 00:02:00 | OFF
01-jan-2016 00:02:01 | ON
01-jan-2016 00:03:00 | OFF

A Sample result would look like, considering
is 01-Jan-2016

State | TotalTime
ON | 00:01:59
OFF | 00:00:11

I'd like to have a query that returns the total time [in hours, mins and secs] for each of the states. Is that possible using SQL Server Express 2012? Any ideas/directions I should take?


A small change would be required if you want to see over 24 hours

Declare @YourTable table (Timestamp datetime,State varchar(25))
Insert Into @YourTable values
('01-jan-2016 00:01:00','ON'),
('01-jan-2016 00:02:00','OFF'),
('01-jan-2016 00:02:01','ON'),
('01-jan-2016 00:03:00','OFF')

Declare @Default DateTime ='01-Jan-2016 00:03:10'

;with cteBase as (
    Select *,NextTime = Lead(TimeStamp,1,@Default) over (Order By TimeStamp)
     From @YourTable
Select State
      ,Duration=cast(DateAdd(SECOND,sum(DateDiff(SECOND,TimeStamp,NextTime)),cast('1900-01-01 00:00:00' as datetime)) as time)
 From  cteBase
 Group By State


State   Duration
OFF     00:00:11
ON      00:01:59

Just a quick note.

Lead(TimeStamp,1,TimeStamp) could be Lead(TimeStamp,1,GetDate()) if you want final state to current.