home

Find gaps in sequence numbers.

Author Nigel Rivett
Three diferent formats
1-7
10-12
12-20
20-25
25-45
45-60

2
3
4
5
6
11
...

2,3,4,5,6,11... 


These scripts work off this table

create table #nos (i int)
insert #nos select 1
insert #nos select 7
insert #nos select 8
insert #nos select 9
insert #nos select 10
insert #nos select 12
insert #nos select 20
insert #nos select 25
insert #nos select 45
insert #nos select 60

To give result in form
1-7
10-12
12-20
20-25
25-45
45-60

select convert(varchar(10),imin.i) + ' - ' +  convert(varchar(10),
(select min(i) from (select i = i from #nos where not exists (select * from #nos a2 where #nos.i-1 = 
a2.i) and #nos.i <> (select min(i) from #nos)) as imax where imax.i > imin.i))
from
(select i = i from #nos where not exists (select * from #nos a2 where #nos.i+1 = a2.i) and #nos.i <> 
(select max(i) from #nos)) as imin

also

select convert(varchar(10), t1.i) + '-' +  convert(varchar(10),t3.i)
from #nos t1
left join #nos t2
on t1.i = t2.i-1
join #nos t3
on t3.i = (select min(t4.i) from #nos t4 where t4.i > t1.i)
where t2.i is null


To give result in form
2
3
4
5
6
11
...


in single statement
This needs a tally table to join to so is limitted by the max value.

select ints.i
from 
(select i = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5 ,
(select i = 0 union select 32) as i6 
) as ints left outer join #nos on ints.i = #nos.i
where #nos.i is null
and ints.i <= 60 and ints.i <> 0
order by ints.i

in loop

temp table
create table #a (i int)
declare @i int
select @i = 0
while @i < (select max(i) from #nos)
begin
set @i = @i + 1
if not exists (select * from #nos where i = @i)
insert #a select @i
end
select * from #a


To give results in form 
2,3,4,5,6,11... 

declare @s varchar(1000)
select @s = coalesce(@s+',' + convert(varchar(10),i),convert(varchar(10),i)) from #a --from (select 
top 100 percent i from #a order by i) as a
select @s


home