CODEWITHSUNDEEP
sqlsql-server
Christian
Christian

Reputation: 1090

Select multiple groups of row from SQL Server result

I am not sure how to title this question but I did my best.. I have a small challenge with some queries using SQL Server 2016.

I have a table with many rows and all of them have a timestamp column. Based on the value of column Lane_0 (value 0 or 1) I need to get the timestamp from date-time column.

The table looks like this:

enter image description here

The groups of rows are marked with yellow.

Basically the table is to keep track of when something is on or off. 1 = on and 0 = off. I need to find the time spent when off (value 0) for each group and also the oldest and newest timestamp for that group. A group is a collection of rows with 0 in Lane_0.

From the picture I would have to get Row_num 7, 8 and 9 and then subtract date_time in row 9 from timestamp in row 7 to get the duration. Finally I need to select date_time in Row_num 7 and 9.

The result should look something like this:

enter image description here

Last picture displays the first group with the three yellow marks. I need a view or SQL query that can select all valid groups from table 1.

Upvotes: 0

Views: 284

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You need to identify "islands" in the data. One method uses a difference of row numbers:

select lane_no, min(datetime), max(datetime), count(*),
       datediff(second, min(datetime), max(datetime)) as dur_second,
       datediff(second, min(datetime), max(datetime))/60.0 as dur_minute
from (select t.*,
             row_number() over (partition by lane_no order by date_time) as seqnum_l,
             row_number() over (order by date_time) as seqnum
      from t
     ) t
where lane_no = 0
group by lane_no, (seqnum - seqnum_l);

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

This is a standard trick with consecutive grouping:

DECLARE @t TABLE
    (
      rn INT ,
      dt DATETIME ,
      ln INT
    )
INSERT  INTO @t
VALUES  ( 5, GETDATE(), 1 ),
        ( 6, GETDATE(), 1 ),
        ( 7, GETDATE(), 0 ),
        ( 8, GETDATE(), 0 ),
        ( 9, DATEADD(ss, 15, GETDATE()), 0 ),
        ( 10, GETDATE(), 1 ),
        ( 11, GETDATE(), 1 ),
        ( 12, GETDATE(), 0 ),
        ( 13, DATEADD(ss, 6, GETDATE()), 0 ),
        ( 14, GETDATE(), 1 );


WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY rn )
                        - ROW_NUMBER() OVER ( ORDER BY ln, rn ) r
               FROM     @t
             )
    SELECT  MIN(dt) ,
            MAX(dt) ,
            DATEDIFF(ss, MIN(dt), MAX(dt))
    FROM    cte
    WHERE   ln = 0
    GROUP BY r

Output:

2017-01-09 15:47:31.560     2017-01-09 15:47:46.560    15
2017-01-09 15:47:31.560     2017-01-09 15:47:37.560    6

Upvotes: 1

Related Questions