By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
| startDate | endDate | interval |
|---|---|---|
| 2016-12-31 00:28:00.000 | 2016-12-31 00:30:00.000 | 1 |
| 2016-12-31 00:30:00.000 | 2016-12-31 01:15:00.000 | 15 |
| 2016-12-31 01:15:00.000 | 2016-12-31 01:19:00.000 | 1 |