SQL select the min and max values of the intersecting interval

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

  Col1  Col2
  1     3
  2     4
  7     12
  7     15
  14    21

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

  Col1  Col2
  1     4
  7     21

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?

Answer

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

with t as (
      select v.*
      from (values (1, 3), (2, 4), (7, 12), (7, 15), (14, 21)
           ) v(col1, col2)
     )
select grp, min(col1), max(col2)
from (select t.*, sum(case when prev_col2 >= col1 then 0 else 1 end) over (order by col1, col2) as grp
      from (select t.*, lag(col2) over (order by col1, col2) as prev_col2
            from t
           ) t
     ) t
group by grp;

Here is a db<>fiddle.