SQL Server: Get time duration based on continues values of a column

Question

I Have a table Like This

ID           VehicleID             Time               EngineStatus
--          ----------      ------------------     ----------------
0              0001          1/11/2016 00:00              off
0              0002          1/11/2016 00:10              off
0              0003          1/11/2016 00:10              off
0              0001          1/11/2016 00:10              ON
0              0001          1/11/2016 00:11              ON
0              0003          1/11/2016 00:20              off
0              0002          1/11/2016 00:15              off
0              0001          1/11/2016 00:11              off
0              0001          1/11/2016 00:12              off
0              0001          1/11/2016 00:20              ON
0              0001          1/11/2016 00:25              ON

I want to get EngineStatus of every vehicle and it duration. I need a query that results like this

     Start              End                 Status        VehileID
---------------    --------------          --------      ----------
1/11/2016 00:00    1/11/2016 00:10           OFF            0001
1/11/2016 00:10    1/11/2016 00:11           ON             0001
1/11/2016 00:11    1/11/2016 00:12           Off            0001
1/11/2016 00:20    1/11/2016 00:25           ON             0001

1/11/2016 00:10    1/11/2016 00:15           OFF            0002

1/11/2016 00:10    1/11/2016 00:20           OFF            0003

I think somehow I have to group the data based on continues values of a column and then get first and last row from each group.

Can you include the version of SQL Server you are using? 2012, and higher, include additional options not available in the earlier versions. — David Rushton, Jan 11 '16 at 12:49
I *think* your question has been downvoted because it doesn't include an example of what you have tried so far (but it's hard to tell because the downvoters haven't included comments). — David Rushton, Jan 11 '16 at 12:52
In your example should vehicle 0001 be off from 00:12 to 00:20. Should there be a record returned for this, creating an unbroken sequence from the first logged time until the last? — David Rushton, Jan 11 '16 at 16:25
Yes there should be data for that time- but it may is not delivered to server yet. — Sepehr Davarnia, Jan 12 '16 at 10:55

Giorgos Betsos · Answer 1 · 2016-01-11T15:24:37.550

Something like this should work:

SELECT MIN([Time]) AS [Start], MAX([Time]) AS [End],
       EngineStatus, VehicleID
FROM (
  SELECT VehicleID, [Time], EngineStatus,
         ROW_NUMBER() OVER (PARTITION BY VehicleID ORDER BY [Time]) -
         ROW_NUMBER() OVER (PARTITION BY VehicleID, EngineStatus
                            ORDER BY [Time]) AS grp
  FROM mytable ) AS t
GROUP BY VehicleID, EngineStatus, grp

grp identifies slices of consecutive records having the same VehicleID, EngineStatus values.

score 0 · Answer 2 · answered Jan 11 '16 at 13:26

Try;

;with data_tbl as (
    select 
        [VehicleID], 
        [Time], 
        [EngineStatus],
        dense_rank() over(partition by [VehicleID] order by [VehicleID], [Time]) rnk
    from tbl    
)
select
    t1.[Time] Start,
    t2.[Time] End,
    t1.[EngineStatus] Status,
    t1.[VehicleID] VehileID   
from data_tbl t1
join data_tbl t2 
on t1.[VehicleID] = t2.VehicleID 
and t1.rnk = t2.rnk - 1
and t1.[EngineStatus] = t2.[EngineStatus]
order by t1.[VehicleID], t1.[Time]

SQL Server: Get time duration based on continues values of a column

2 Answers2