Can I use SQL to find missing numbers in the examp

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

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_block
FROM
     dbo.My_Table T1
INNER JOIN dbo.My_Table T2 ON
     T2.start_block > T1.end_block
LEFT OUTER JOIN dbo.My_Table T3 ON
     T3.start_block > T1.end_block AND
     T3.start_block < T2.start_block
WHERE
     T3.id IS NULL AND
     T2.start_block <> T1.end_block + 1

Answer 2

 Select * From Table O
   Where 
      (Exists
         (Select * From Table
          Where End_Block < O.Start_Block)
       And Not Exists 
         (Select * From Table
          Where End_Block = O.Start_Block - 1)) 
    Or
      (Exists
         (Select * From Table
          Where Start_Block > O.End_Block)
       And Not Exists 
         (Select * From Table
          Where Start_Block = O.End_Block + 1 ))