DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 31--------------------------------------------------
       To find gaps in numbers. including negative numbers 
       By: srinivasma_exceldbp Date: March 21, 2014, 5:07 am
       ---------------------------------------------------------
       -- To find gaps in numbers. works for negative numbers also
       -- gaps are out put as startgap and endgap ( both inclusive)
       declare @tempnum table (numbers int not null primary key)
       insert into @tempnum values (1)
       insert into @tempnum values (3)
       insert into @tempnum values (7)
       insert into @tempnum values (9)
       insert into @tempnum values (1000)
       insert into @tempnum values (-1)
       insert into @tempnum values (-4)
       insert into @tempnum values (-8)
       insert into @tempnum values (-10)
       select (numbers + 1) as startgap, (nextnumber - 1) as endgap
       from
       (
       select numbers,
       (
       select min(numbers) from @tempnum as b
       where b.numbers > a.numbers) as nextnumber
       from @tempnum as a) as d
       where (nextnumber - numbers) > 1
       *****************************************************