(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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 *****************************************************