Combine multiple rows with intervals

Question

I have table with columns with left and right value. Each row represent a section that starts on leftvalue and ends on rightvalue.

LeftValue   RightValue
----------- -----------
1           2
3           4
8           9
10          11
12          13
14          15
16          18
20          25
26          27

I want to optimize it to minimal number of contentious sections. For data above excepted result is

LeftValue   RightValue
----------- -----------
1           4
8           18
20          27

Some sections have been merged into one. For example 1-2 and 3-4 into 1-4.

Is there some efficient way to do this in sql? I did it with cursor by that approach is too slow.

this has been answered here: https://dba.stackexchange.com/questions/100965/combining-separate-ranges-into-largest-possible-contiguous-ranges or for sql server: https://stackoverflow.com/questions/6068619/merging-date-intervals-in-sql-server — nico boey, Aug 23 '17 at 09:07

score 0 · Accepted Answer · answered Aug 23 '17 at 08:57

You can use lead/lag if you are using SQL Server 2012+ as below:

;With cte as (
Select *, Bucket = Sum(Nextright) over(order by leftvalue) from (
    Select *, Nextright = case when leftvalue - lag(rightvalue) over(order by leftvalue) <> 1 then 1 else 0 end 
        from #data
    ) a  
) Select min(leftvalue) as leftvalue, Max(rightvalue) as rightvalue
    from cte
    group by bucket

Output as below:

+-----------+------------+
| leftvalue | rightvalue |
+-----------+------------+
|         1 |          4 |
|         8 |         18 |
|        20 |         27 |
+-----------+------------+

Combine multiple rows with intervals

1 Answers1