CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
MattH
MattH

Reputation: 4227

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

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.

Upvotes: 6

Views: 2520

Answers (6)

Daniel PP Cabral
Daniel PP Cabral

Reputation: 1624

declare @t table (num int)

insert @t values 
(2),(3),(4),(5),
(1001),(1002),(1003),(1004),(1005),
(999101),(999102),(999103),(999104),(999105)

;with cte as
(
    select num,(ROW_NUMBER() OVER(ORDER BY num)) + 1 as idx from @t
    union
    select 0 [num],1 [idx] --start boundary
    union
    select 1000000 [num],COUNT(num) + 2 [idx] from @t --end boundary
)
select c1.num + 1 [Start], c2.num - 1 [End] 
from cte c1
inner join cte c2 on c2.idx = c1.idx + 1
where c2.num != c1.num + 1

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44356

declare @t table(id int)
insert @t values
(1),(2),(3),(4),(5),(1001),(1002),(1003),(1004),(1005),
(999101),(999102),(999103),(999104),(999105)

select t1.id+1 [start], coalesce(t3.[end], 999999) [end] 
from @t t1
left join @t t2 on t1.id +1 = t2.id
cross apply
(select min(id)-1 [end] from @t where t1.id < id
) t3
where t2.id is null

Upvotes: 1

valex
valex

Reputation: 24144

Here is SQLFiddle demo

select 

case when max(n1)=0 then 1 else max(n1)end,
case when max(n2)=0 then 999999 else max(n2)end

from
(
select t.n+1 as n1,0 n2,
   row_number() over(order by t.n)
      +isnull((select 0 from t where n=1),1)  
      rn  
from t
left join t t2 on t.n+1=t2.n
where t2.n is null

union all 

select 0 n1, t.n-1 as n2 ,
row_number() over(order by t.n) rn 
from t
left join t t2 on t.n-1=t2.n
where t2.n is null
  and t.n>1
) t3
group by rn

Upvotes: 1

WKordos
WKordos

Reputation: 2255

create table #temp (id int)

insert into #temp (id)
values (1),(2),(3),(1000),(1001),(1002),(2000)

--drop table #temp
with cte as
(
select *, ROW_NUMBER() over(order by id) as rn
from #temp a 
)
select a.id + 1, b.id - 1
from cte a join cte b on a.rn = b.rn - 1 and a.id <> b.id -1

it wont include tail ranges, like 2001-9999

Upvotes: 1

Kevin Horgan
Kevin Horgan

Reputation: 1580

if you have a table called "kh" for example with a column "myval" which is your list of integers you could try this SELECT. 

SELECT MAX(t1.myval+1) AS 'StartRange',t3.myval-1 AS 'EndRange' 
FROM kh t1, kh t3
WHERE t1.myval+1 NOT IN (SELECT myval FROM kh t2 ORDER BY myval)
AND t3.myval-1 NOT IN (SELECT myval FROM kh t4 ORDER BY myval)
AND t1.myval < t3.myval
GROUP BY  t3.myval
ORDER BY StartRange

Upvotes: -1

podiluska
podiluska

Reputation: 51514

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

Upvotes: 5

Related Questions