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