CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Codrut Rotaru
Codrut Rotaru

Reputation: 61

SQL create time intervals where other column value is the same

I have a SQL Server table that stores an event with datetime, like this:

ES  TimeStamp
----------------------------
1   2018-03-05 00:00:12.000
1   2018-03-05 00:00:32.000
0   2018-03-05 00:00:52.000
0   2018-03-05 00:01:12.000
1   2018-03-05 00:01:32.000
1   2018-03-05 00:01:52.000
0   2018-03-05 00:02:12.000
0   2018-03-05 00:02:32.000
0   2018-03-05 00:02:52.000
0   2018-03-05 00:03:12.000
1   2018-03-05 00:03:32.000
1   2018-03-05 00:03:52.000
1   2018-03-05 00:04:12.000

I am looking for a fast way to select from my table:

ES   StartTimeStamp             EndTimeStamp
-------------------------------------------------------
1    2018-03-05 00:00:12.000    2018-03-05 00:00:32.000
0    2018-03-05 00:00:52.000    2018-03-05 00:01:12.000
1    2018-03-05 00:01:32.000    2018-03-05 00:01:52.000
0    2018-03-05 00:02:12.000    2018-03-05 00:03:12.000
1    2018-03-05 00:03:32.000    2018-03-05 00:04:12.000

Upvotes: 2

Views: 161

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use Gaps and Islands solution.

SELECT ES, MIN([TimeStamp]) StartTimeStamp, MAX([TimeStamp]) EndTimeStamp FROM 
    (SELECT *, 
        GRP = ROW_NUMBER() OVER(ORDER BY [TimeStamp]) - 
            ROW_NUMBER() OVER(PARTITION BY ES ORDER BY [TimeStamp])  FROM @MyTable) AS T
GROUP BY ES, GRP

Sample Data:

DECLARE @MyTable TABLE (ES INT ,[TimeStamp] DATETIME)
INSERT INTO @MyTable VALUES
(1, '2018-03-05 00:00:12.000'),
(1, '2018-03-05 00:00:32.000'),
(0, '2018-03-05 00:00:52.000'),
(0, '2018-03-05 00:01:12.000'),
(1, '2018-03-05 00:01:32.000'),
(1, '2018-03-05 00:01:52.000'),
(0, '2018-03-05 00:02:12.000'),
(0, '2018-03-05 00:02:32.000'),
(0, '2018-03-05 00:02:52.000'),
(0, '2018-03-05 00:03:12.000'),
(1, '2018-03-05 00:03:32.000'),
(1, '2018-03-05 00:03:52.000'),
(1, '2018-03-05 00:04:12.000')

Result:

ES          StartTimeStamp          EndTimeStamp
----------- ----------------------- -----------------------
1           2018-03-05 00:00:12.000 2018-03-05 00:00:32.000
0           2018-03-05 00:00:52.000 2018-03-05 00:01:12.000
1           2018-03-05 00:01:32.000 2018-03-05 00:01:52.000
0           2018-03-05 00:02:12.000 2018-03-05 00:03:12.000
1           2018-03-05 00:03:32.000 2018-03-05 00:04:12.000

Upvotes: 3

D-Shih
D-Shih

Reputation: 46219

You can use LAG to make the group,then group by the subquery will get the expect.

SELECT  t.ES ,
        MIN(t.TimeStamp) AS 'StartTimeStamp' ,
        MAX(t.TimeStamp) AS 'EndTimeStamp'
FROM    ( SELECT    * ,
                    MAX(flag) OVER ( ORDER BY t.TimeStamp ) rn
          FROM      ( SELECT    * ,
                                CASE WHEN t.ES = LAG(t.ES) OVER ( ORDER BY TimeStamp )
                                     THEN 0
                                     ELSE t.id
                                END flag
                      FROM      ( SELECT    * ,
                                            ROW_NUMBER() OVER ( ORDER BY TimeStamp ) AS id
                                  FROM      T
                                ) AS t
                    ) AS t
        ) AS t
GROUP BY t.es ,
        t.rn

SQLFiddle

Upvotes: 1

Related Questions