Skip to content
Advertisement

SQL select the min and max values of the intersecting interval

In MsSQL 2014 I`ve got a table containing intervals of values

I need to get the maximum and minimum values of the intersecting intervals. Expected result:

Have ideas how to build a query?

The first row describes the interval from 1 to 3. That is, the interval contains the values 1, 1.1, 1.2, …, 2.8, 2.9, 3. The interval on the second row contains the values 2, 2.1, 2.2 …3.9, 4. the intersection of the intervals in the first two rows contains 2, 2.1,…2.9, 3.

And in the fifth line the interval has no intersections with the interval from the third row, but the fifth row and the third have intersections with the fourth row.

I need to find such intervals and get their minimum and maximum values. I know how to do this with the cursor, but can I do it with a SQL query?

Advertisement

Answer

This is a gaps and islands problem. In this case, you want to find where the “islands” start (that is no previous overlap):

Here is a db<>fiddle.

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