Define ranges to cover gaps in a number sequence (T-SQL)

Question

Simplifying my problem down - I have 6-digit field which assigns numbers to customers starting from 1 and ending to 999999. Most numbers are sequentially assigned, but numbers can be assigned manually by users, and this feature has been used in an unpredicatable pattern throughout the range.

We now need to identify numbers that have not been assigned (easy) - and then convert this into a number of ranges (seems complex).

For example given the following numbers have been assigned

1,2,3,4,5,
1001,1002,1003,1004,1005,
999101,999102,999103,999104,999105

I need a resulting set of ranges like

Start  End
6      1000
1006   999100
999106 999999

My thinking so far is this is probably too complex to write in queries - and best achieved by looping from 1 to 999999, and adding ranges to a temp table.

Interested to hear ideas as I can imagine there are a few approaches. I'm using SQL Server 2008 R2. This is a one-off exercise so even a non-SQL solution might be appropriate, if this were for example easily done in Excel.

Answer 1

Try this

declare @t table (num int)

insert @t values (2),(3),(6),(7),(9),(10),(11)

select 
    MIN(number) as rangestart,
    MAX(number) as rangeend
from
(
    select *,
        ROW_NUMBER() over (order by number) -
        ROW_NUMBER() over (order by num,number) grp
    from 
    (   
        select number from master..spt_values where type='p' and number between 1 and 15 
    ) numbers 
    left join @t t
        on numbers.number = t.num       
) v
where num is null
group by grp

Reference : gaps and islands by itzik ben-gan

To create a numbers query upto 999999

select p1.number +  p2.number * 2048 as number
from 
(select * from master..spt_values where type='p' ) p1,
(select * from master..spt_values where type='p' and number<489) p2    
where p1.number +  p2.number * 2048 <=999999