CODEWITHSUNDEEP
sqlsql-server
D. Caan
D. Caan

Reputation: 2019

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 NOW is 01-Jan-2016 00:03:10.

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?

Upvotes: 0

Views: 45

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

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

Returns

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.

Upvotes: 1

Related Questions