Finding Missing/Skipped Numbers in table in SQL Server
Declare @tbl table(slno int)
insert into @tbl(slno) values(1)
insert into @tbl(slno) values(3)
insert into @tbl(slno) values(4)
insert into @tbl(slno) values(5)
insert into @tbl(slno) values(11)
insert into @tbl(slno) values(13)
insert into @tbl(slno) values(18)
insert into @tbl(slno) values(8008)
select * from @tbl
select c.slno from
(select a.slno,(select b.slno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) b where b.ActSlNo=a.ActSlNo+1) as nextSlno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) a) c
where ISNULL(c.nextSlno,0)-ISNULL(c.slno,0)>1
insert into @tbl(slno) values(1)
insert into @tbl(slno) values(3)
insert into @tbl(slno) values(4)
insert into @tbl(slno) values(5)
insert into @tbl(slno) values(11)
insert into @tbl(slno) values(13)
insert into @tbl(slno) values(18)
insert into @tbl(slno) values(8008)
select * from @tbl
select c.slno from
(select a.slno,(select b.slno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) b where b.ActSlNo=a.ActSlNo+1) as nextSlno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) a) c
where ISNULL(c.nextSlno,0)-ISNULL(c.slno,0)>1
No comments:
Post a Comment