Find Filled Ranges in a Sequence
By Kalman Toth, M.Phil., M.Phil., MCDBA

Frequently you may have a sequence with "holes". For an integer sequence, you can find the existing ranges with a single T-SQL query. For date ranges, it may require a multi-step script.

First we create a temporary table for testing - a table variable would do as well:

create table #Seq (ID int)
--drop table #Seq

Populate it with an integer sequence of 1 to 1000:

declare @i int
set @i = 1

while @i < 1000
begin
insert #Seq select @i

set @i=@i+1
end

Create "holes" in the sequence:

-- select * from #Seq
delete #Seq where id = 22
delete #Seq where id = 23
delete #Seq where id = 24
delete #Seq where id = 25
delete #Seq where id = 42
delete #Seq where id = 72
delete #Seq where id = 73

delete #Seq where id = 499
delete #Seq where id = 501;

The solution query applies 2 CTE-s: range start points and end points. The new EXCEPT operation is uppercased for attention getting. The "not in" operator can also be used instead of the EXCEPT. The latter though is neater logically. For range start points anything is eliminated which has a predecessor. While for range end, anything is eliminated which has a successor. In the main query we need the magic of a GROUP BY to reduce the result set for the desired pairs of numbers.

with cteStart as
(select id from #Seq
EXCEPT
select b.id
from #Seq a, #Seq b
where a.id + 1 = b.id),

cteEnd as (select id from #Seq
EXCEPT
select b.id
from #Seq a, #Seq b
where a.id = b.id+1)

select start, [end]=min([end])
from (select start=st.id, [end]=en.id
from cteStart st, cteEnd en
where st.id <= en.id) pair
group by start
order by start