HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

T-SQL to Group time interval change by date range in sql server

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
groupsqlrangeintervaltimedateserverchange

Problem

The original table has the column Timestamp with Interval

Interval: difference in minute between the current and previous Timestamp when sorted by Timestamp itself

Timestamp               Interval(InMinute)
2016-12-31 00:28:00     NULL
2016-12-31 00:29:00     1
2016-12-31 00:30:00     1
2016-12-31 00:45:00     15
2016-12-31 01:00:00     15
2016-12-31 01:15:00     15
2016-12-31 01:16:00     1
2016-12-31 01:17:00     1
2016-12-31 01:18:00     1
2016-12-31 01:19:00     1


I want to detect a time interval change using T-SQL and produce the output

StartDate                EndDate                Interval
2016-12-31 00:28:00      2016-12-31 00:30:00    1
2016-12-31 00:30:00      2016-12-31 01:15:00    15
2016-12-31 01:15:00      2016-12-31 01:19:00    1


I wanted to tell for how long an Interval stayed the same. The second row says that from 2016-12-31 00:30:00 to 2016-12-31 01:15:00, the Interval stayed the same i.e. 15. But then it changed back to 1 after 2016-12-31 01:15:00

Solution

there is an example , from Itzik Ben Gan (Gaps and Islands in Sequences)
This is base on this article: Gaps

DECLARE @vt_Source AS TABLE
( ts datetime NOT NULL PRIMARY KEY,
 interval tinyint NULL
)

INSERT INTO @vt_Source(ts, interval)
VALUES('2016-12-31 00:28:00',     NULL)
,('2016-12-31 00:29:00'  ,   1)
,('2016-12-31 00:30:00'  ,   1)
,('2016-12-31 00:45:00'  ,   15)
,('2016-12-31 01:00:00'  ,   15)
,('2016-12-31 01:15:00'  ,   15)
,('2016-12-31 01:16:00'  ,   1)
,('2016-12-31 01:17:00'  ,   1)
,('2016-12-31 01:18:00'  ,   1)
,('2016-12-31 01:19:00'  ,   1)

SELECT
   min(ts_prev) AS startDate
  ,max(ts) AS endDate
  ,interval
FROM
   (SELECT
         ts
         ,interval
         ,ROW_NUMBER() OVER(ORDER BY ts ASC) AS  rn_all
         ,ROW_NUMBER() OVER(PARTITION BY interval ORDER BY ts ASC) AS rn_group
         ,LAG(ts,1,ts) OVER(ORDER BY ts ASC) AS ts_prev
    FROM
        @vt_Source
   )A
WHERE
   A.interval IS NOT NULL
GROUP BY
   rn_all - rn_group
   ,interval
ORDER BY 
   startDate ASC


output for this:

startDate           endDate          interval
31/12/2016 00:28:00 31/12/2016 00:30:00 1
31/12/2016 00:30:00 31/12/2016 01:15:00 15
31/12/2016 01:15:00 31/12/2016 01:19:00 1


I added a WHERE clause to eliminate the first row , the one that have NULL on interval column

http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=fe1888d0a934d73de0ed9887aaf4d482

Code Snippets

DECLARE @vt_Source AS TABLE
( ts datetime NOT NULL PRIMARY KEY,
 interval tinyint NULL
)

INSERT INTO @vt_Source(ts, interval)
VALUES('2016-12-31 00:28:00',     NULL)
,('2016-12-31 00:29:00'  ,   1)
,('2016-12-31 00:30:00'  ,   1)
,('2016-12-31 00:45:00'  ,   15)
,('2016-12-31 01:00:00'  ,   15)
,('2016-12-31 01:15:00'  ,   15)
,('2016-12-31 01:16:00'  ,   1)
,('2016-12-31 01:17:00'  ,   1)
,('2016-12-31 01:18:00'  ,   1)
,('2016-12-31 01:19:00'  ,   1)


SELECT
   min(ts_prev) AS startDate
  ,max(ts) AS endDate
  ,interval
FROM
   (SELECT
         ts
         ,interval
         ,ROW_NUMBER() OVER(ORDER BY ts ASC) AS  rn_all
         ,ROW_NUMBER() OVER(PARTITION BY interval ORDER BY ts ASC) AS rn_group
         ,LAG(ts,1,ts) OVER(ORDER BY ts ASC) AS ts_prev
    FROM
        @vt_Source
   )A
WHERE
   A.interval IS NOT NULL
GROUP BY
   rn_all - rn_group
   ,interval
ORDER BY 
   startDate ASC
startDate           endDate          interval
31/12/2016 00:28:00 31/12/2016 00:30:00 1
31/12/2016 00:30:00 31/12/2016 01:15:00 15
31/12/2016 01:15:00 31/12/2016 01:19:00 1

Context

StackExchange Database Administrators Q#171106, answer score: 4

Revisions (0)

No revisions yet.