Calculate Time Between On And Off Status Rows Sql Server

I´m using SQL Server 2008, how can I calculate the time between On and Off status? I have the following table (ordered by timestamp): ID | EQUIP_ID | TIMESTAMP | STATUS

Solution 1:

This approach first filters out the repeated on and repeated off rows. With only status switches left, the "end time" can be retrieved by searching for the next row based on row_number.

; with  numbered as
        (
        select  row_number() over (partition by equip_id orderby timestamp) rn
        ,       *
        from    YourTable
        )
,       nodups as
        (
        select  row_number() over (partition by cur.equip_id orderby cur.timestamp) rn
        ,       cur.equip_id
        ,       cur.timestamp
        ,       cur.status
        from    numbered cur
        left join    
                numbered prev
        on      cur.rn = prev.rn + 1and cur.status = prev.status
        where   prev.id is null
        )
select  cur.rn
,       cur.equip_id
,       cur.timestamp as StartTime
,       next.timestamp as EndTime
,       cur.status
from    nodups cur
left join    
        nodups nextonnext.rn = cur.rn + 1andnext.equip_id = cur.equip_id

Live example at SQL Fiddle.

Solution 2:

Here's my take on it. Assuming your table is called "MyData":

WITH operating AS
(
    SELECT
        d.EQUIP_ID
        , d.[TIMESTAMP]
        , d.[STATUS]
        , ROW_NUMBER() OVER (PARTITIONBY EQUIP_ID ORDERBY [TIMESTAMP]) RowNum
        , ROW_NUMBER() OVER (PARTITIONBY EQUIP_ID ORDERBY [TIMESTAMP]) -ROW_NUMBER() OVER (PARTITIONBY EQUIP_ID, [STATUS] ORDERBY [TIMESTAMP]) AS [Group]
    FROM 
        MyData d
)
SELECT 
    state1.EQUIP_ID
    , MIN(state1.[TIMESTAMP]) [START]
    , MAX(state2.[TIMESTAMP]) [END]
    , state1.STATUS 
FROM 
    operating state1
LEFTJOIN
    operating state2 
    ON 
    state1.RowNum = state2.RowNum -1WHERE
    state2.[TIMESTAMP] ISNOTNULLGROUPBY  
    state1.EQUIP_ID, state1.[STATUS], state1.[Group]
ORDERBYMIN(state1.[TIMESTAMP])

It makes use of the ROW_NUMBER() function to determine changes in statuses for each EQUIP_ID. Then it simply finds when a status started (MIN([TIMESTAMP])), then I match it up with the time it ended (MAX([TIMESTAMP])) in the next row (see the self-join on RowNum). The WHERE eliminates the last row which has no end time. The results I get are:

EQUIP_ID|START|END|STATUS---------+-------------------------+-------------------------+-------1|2012-05-21 13:00:00.000|2012-05-21 13:05:00.000|11|2012-05-21 13:05:00.000|2012-05-21 13:09:00.000|01|2012-05-21 13:09:00.000|2012-05-21 13:13:00.000|11|2012-05-21 13:13:00.000|2012-05-21 13:14:00.000|01|2012-05-21 13:14:00.000|2012-05-21 13:15:00.000|1