-- // CREATE A TABLE
Create table tb_GAPS
(
Sno int not null
)
Go
--// Populate dummy records in the table:
Insert tb_GAPS values (15)
Insert tb_GAPS values (10)
Insert tb_GAPS values (3)
Insert tb_GAPS values (5)
Insert tb_GAPS values (9)
Insert tb_GAPS values (11)
------------------
--// HERE IS THE CODE
Declare @MAX as int
Declare @Start as int
Declare @SRN as int
SET @Start =1
SET @SRN =0
select @MAX=max(Sno) from tb_GAPS
--select @Start=min(Sno) from tb_GAPS
DECLARE @m_TempTable AS TABLE (
[SNO] [INT]
)
WHILE (@Start < = @MAX )
BEGIN
select @SRN=sno from tb_GAPS where sno = @Start
IF (@SRN = 0)
BEGIN
INSERT into @m_TempTable values(@Start)
END
SET @Start = @Start+1
SET @SRN =0
END
select * from @m_TempTable
1 comment:
Here is the code that I found somewhere from the net. I think it is also a better option if you know something about the CTE and Recursion queries.
you just need to create a table named as "tblFindGaps" and fill it with some dummy data.
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
Post a Comment