Archive

Archive for August, 2013

T-SQL : Gaps and Islands Problem – 2

There are lot of ways to find the gaps in a sequential numbers, and some of them are listed below. I have posted some 3 methods to achieve the expected. The goal is to find the gaps between the sequential number. The query should return the Gap start point and Gap end point. Please check these queries and any suggestions/comments are always welcomed.

declare @table table (num int)
insert into @table
select 1 union all
select 2 union all
select 3 union all
select 6 union all
select 8 union all
select 11 union all
select 12 union all
select 13 union all
select 15 union all
select 17 union all
select 28

--Query 1
select GapStart,GapEnd from
( select m.num+ 1 as GapStart
,(select min(num) - 1 from @table as x where x.num > m.num) as GapEnd
from @table as m left outer join @table as r on m.num = r.num- 1 where r.num is null
) as x where GapEnd is not null 

--Query 2 
select a.num1 GapStart,b.num2 GapEnd from 
(select row_number()over(order by num) ID,num,num+1 num1 ,num-1 num2 from @table)a,
(select row_number()over(order by num) ID,num,num+1 num1 ,num-1 num2 from @table)b
where a.ID+1 = b.ID
and (a.num1<b.num2 or a.num1 = b.num2) 

--Query 3 
select GapStart, GapEnd from 
(select m.num,m.num+1 as GapStart,ROW_NUMBER() over (order by m.num+1) as ser
from @table m
left join (select num-1 as rowno from @table) mm
on m.num=mm.RowNo
where mm.RowNo is null and
m.num+1<(select MAX(num) from @table ))x
join 
(select m.num,m.num-1 as GapEnd,ROW_NUMBER() over (order by m.num-1) as ser from @table m
left join (select num+1 as rowno from @table) mm
on m.num=mm.RowNo
where mm.RowNo is null and
m.num-1>(select MIN(num) from @table ))y
on x.ser=y.ser

Output:
Capture

Happy Querying 😉

Advertisements
%d bloggers like this: