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

HighAbove

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

HighAbove

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related