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

Here is a SQL that actually tells you the missing rows!

Did it quite fast so ignore performance issues:

Based on:

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)
insert into #t (startz, zend) values (45,58)
insert into #t (startz, zend) values (60,64)
insert into #t (startz, zend) values (70,98)


select tab1.zend+1 as MissingStartValue,
       (select min(startz-1) from #t where startz > tab1.zend+1) as MissingEndValue
 from #t as tab1 where not exists (select 1 from #t as tab2 where tab1.zend + 1 = tab2.startz)
and (select min(startz-1) from #t where startz > tab1.zend+1) is not null 

Answer 2

select e1.end_block + 1 as start_hole,
    (select min(start_block) 
     from extent e3 
     where e3.start_block > e1.end_block) - 1 as end_hole
from extent e1 
left join extent e2 on e2.start_block = e1.end_block + 1
where e2.start_block is null 
and e1.end_block <> (select max(end_block) from extent);

Although I'd say this is a reasonable candidate for iterating through the result in TSQL: you're going to have to scan the entire table (or at least the entirety of indices on start_block and end_block) anyway, so looping through just once and using variables to remember the last value is something to aim for.

Answer 3

SELECT t1.End_Block + 1 as Start_Block,
       t2.Start_Block - 1 as End_Block,
  FROM Table as t1, Table as t2
 WHERE t1.ID + 1 = t2.ID
   AND t1.End_Block + 1 <> T2.Start_Block 

This assumes the IDs in the table are sequential. If they are not sequential then you have to do some complicated linking with Start_Block to End_Block to link the two blocks adjacent to each other.

Answer 4

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 5

select * from blocks a
where not exists (select * from blocks b where b.start_block = a.end_block + 1)

would give you the blocks immediately preceding a gap. You could get fancy. Let's see...

select a.end_block, min(b.start_block)
from blocks a,
     blocks b
where not exists (select * from blocks c where c.start_block = a.end_block + 1)
and b.start_block > a.end_block
group by a.end_block

I think that ought to do it.

Answer 6

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