patternsqlMinor
T-SQL to Group time interval change by date range in sql server
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
I want to detect a time interval change using T-SQL and produce the output
I wanted to tell for how long an Interval stayed the same. The second row says that from
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 1I 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 1I 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:00Solution
there is an example , from Itzik Ben Gan (Gaps and Islands in Sequences)
This is base on this article: Gaps
output for this:
I added a
http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=fe1888d0a934d73de0ed9887aaf4d482
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 ASCoutput 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 1I added a
WHERE clause to eliminate the first row , the one that have NULL on interval columnhttp://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 ASCstartDate 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 1Context
StackExchange Database Administrators Q#171106, answer score: 4
Revisions (0)
No revisions yet.