Tuesday, May 01, 2007

Find the missing numbers (GAPS) within a table...

In this post I have given one of the way to find out the missing numbers within a table. Please note the you need SQL Server 2005 to execute this example and test it yourself.

Sample table creation

Create table tblFindGaps
(
Sno int not null
)
Go

Populate dummy records in the table:

Insert tblFindGaps values (1)
Insert tblFindGaps values (10)
Insert tblFindGaps values (3)
Insert tblFindGaps values (5)
Insert tblFindGaps values (9)
Insert tblFindGaps values (11)
Insert tblFindGaps values (15)
Insert tblFindGaps values (18)
Insert tblFindGaps values (22)
Insert tblFindGaps values (100)
Go

Solution to find the missed out numbers:

Declare @intMaxNum int
Select @intMaxNum = max(Sno) from tblFindGaps;

With tempData (result) as
(
Select distinct FG.Sno + 1 from tblFindGaps FG where not exists
(
Select 1 from tblFindGaps FGP where FGP.Sno = FG.Sno + 1
) and FG.Sno < @intMaxNum

Union All

Select TD.result + 1 from tempData TD where not exists
(
Select 1 from tblFindGaps FGP where FGP.Sno = TD.result + 1
) and TD.result < @intMaxNum
)
Select result as 'Missing Numbers' from tempData order by result;

No comments: