Can I use SQL to find missing numbers in the example table I give below?
Sure wouldn't hurt to give it a try
CREATE TABLE #t (startz INT, zend INT)insert into #t (startz, zend) values (1,5)insert into #t (startz, zend) values (6,11)insert into #t (startz, zend) values (12,18)insert into #t (startz, zend) values (25,31)insert into #t (startz, zend) values (32,43)select * from #t taLEFT OUTER JOIN #t tb ON tb.startz - 1 = ta.zendWHERE tb.startz IS NULLThe last result is a false positive. But easy to eliminate.
You could try:
SELECT t.ID, t.Start_Block, t.End_BlockFROM [TableName] tJOIN [TableName] t2 ON t.ID = t2.ID+1WHERE t.Start_Block - t2.End_Block > 1
This will do it. You might also want to look for overlapping blocks.
SELECT T1.end_block + 1 AS start_block, T2.start_block - 1 AS end_blockFROM dbo.My_Table T1INNER JOIN dbo.My_Table T2 ON T2.start_block > T1.end_blockLEFT OUTER JOIN dbo.My_Table T3 ON T3.start_block > T1.end_block AND T3.start_block < T2.start_blockWHERE T3.id IS NULL AND T2.start_block <> T1.end_block + 1