Can I use SQL to find missing numbers in the example table I give below?

Question

Let's say I have the following table with three columns:

id | start_block | end_block
-----------------------------
01 | 00000000001 | 00000000005
02 | 00000000006 | 00000000011
03 | 00000000012 | 00000000018
04 | 00000000025 | 00000000031
05 | 00000000032 | 00000000043

Each row was a "Start Block" and an "End Block". If the data was perfect, each start block would be one more than the end block before it. So, for row id == 02, the start block is 6 while the end block for the row before it 5.

I need to query this data (it's tens of thousands of row) and find any missing rows. According to my sample data, there should be a row in between 03 and 04 that has a start block of 19 and an end block of 24.

I'm trying to build a report in JSP to reconcile this data and find the missing rows. The ugly way to do this would be to pull the whole recordset into an array and do something like on every row:

if ((arry(i,1) + 1) != (arry(i+1),1)( {
  print("Bad Row!\n");
}

But, I would really like to be able to query the recordset directly and return what I need. Is that possible? If not, could someone point me in the right direction of creating a stored proc that does what I need?

Answer 1

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 ta
LEFT OUTER JOIN #t tb ON tb.startz - 1 = ta.zend
WHERE tb.startz IS NULL

The last result is a false positive. But easy to eliminate.

Answer 2

You could try:

SELECT t.ID, t.Start_Block, t.End_Block
FROM [TableName] t
JOIN [TableName] t2 ON t.ID = t2.ID+1
WHERE t.Start_Block - t2.End_Block > 1