Find Missing Numbers in a Sequence

  • Thread starter Thread starter Gary Dolliver
  • Start date Start date
G

Gary Dolliver

Hi everyone,
I have a table that is imported to daily, and sometimes orders are not
correctly migrated into the system. As all numbers that come in are in
numerical order, is there a way to create a query that will return a result
set of numbers from the sequence that are missing, based on the highest and
lowest value of the number sequence?
Help is always appreciated, thanks!
-gary
 
The missing numbers have to come from somewhere, to create a table that has
the entire sequence of numbers you could need, from lowest to highest. You
can use the code at the end of this page to populate it if you wish:
http://allenbrowne.com/ser-39.html
(The example populates from 1 to 1000, but you can change the loop easily
enough.)

Now use the Unmatched Query Wizard to get all the numbers in this counter
table that are not in your actual table.
 
This finds the first missing but sequence must start at 1 --
SELECT TOP 1 [ID]-1 AS Missing
FROM Your_Table AS Q
WHERE ((((SELECT COUNT(*) FROM Your_Table Q1
WHERE Q1.ID < Q.ID)+1)<>[ID]))
ORDER BY [ID]-1;
 
To list the start of the gap you can use

SELECT A.ID+1 as GapStart
FROM YourTable as A LEFT JOIN YourTable As B
ON A.ID + 1= B.ID
WHERE B.ID is Null
AND A.ID Between 25 and 95

This will list the start of the gap.
For instance if you were checking 25 to 30 and the records were 25,28,30,31
this should return 26 and 29. Note that it doesn't detect that 27 is also
missing. The simplest solution to catch every item that is missing would be
to use an auxiliary table that contains nothing but a sequence of numbers
from 1 to the highest number you expect to see. With that the query becomes

SELECT S.TheNumber
FROM SequenceTable as S LEFT JOIN YourTable
ON S.TheNumber = YourTable.ID
WHERE S.TheNumber Between 25 and 95


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

KARL DEWEY said:
This finds the first missing but sequence must start at 1 --
SELECT TOP 1 [ID]-1 AS Missing
FROM Your_Table AS Q
WHERE ((((SELECT COUNT(*) FROM Your_Table Q1
WHERE Q1.ID < Q.ID)+1)<>[ID]))
ORDER BY [ID]-1;

--
KARL DEWEY
Build a little - Test a little


Gary Dolliver said:
Hi everyone,
I have a table that is imported to daily, and sometimes orders are not
correctly migrated into the system. As all numbers that come in are in
numerical order, is there a way to create a query that will return a
result
set of numbers from the sequence that are missing, based on the highest
and
lowest value of the number sequence?
Help is always appreciated, thanks!
-gary
Click to expand...
Click to expand...
 
Back
Top