Skip to content
Advertisement

Date cycle in T-SQL

I need to write a query that displays time intervals that go on without interruption. Example:

Input:

Output:

I think we need to use the function DATEDIFF line by line. The problem is that I don’t know how to access indexes. Or can this problem be solved much easier?

Advertisement

Answer

This is a type of gaps-and-islands problem. Use a lag() to see if adjacent rows overlap. Do a cumulative sum of gaps, and then aggregate:

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement