Find non sequential numbers

[Tara]

Is there a way to find numbers that are missing from a group? For instance,
find numbers that don't follow sequence. We have a receipts database to
track sales of a fundraising item. I need to figure out which receipts are
missing from the group so that we know whether or not there are outstanding
receipts that our staff haven't turned in yet.

Thanks!

 

[Lou]

Is there a way to find numbers that are missing from a group?  For instance,
find numbers that don't follow sequence.  We have a receipts database to
track sales of a fundraising item.  I need to figure out which receiptsare
missing from the group so that we know whether or not there are outstanding
receipts that our staff haven't turned in yet.

Thanks!

If it can be assumed that there are not a bunch of missing receipts,
you could try this:

SELECT A.[Receipt#] + 1 as [Missing Receipt]
from tblSales as A
where not exists
( select 'true'
from tblSales
where [Receipt#] = A.[Receipt#] + 1 ) ;

However, it will only select the first of a missing sequence of
multiple missing receipts.
Further, there may be some missing receipts before the first one
found.

 

[Tara]

Thanks for the response Lou. I'll give it a try and let you know how it goes!

Is there a way to find numbers that are missing from a group? For instance,
find numbers that don't follow sequence. We have a receipts database to
track sales of a fundraising item. I need to figure out which receipts are
missing from the group so that we know whether or not there are outstanding
receipts that our staff haven't turned in yet.

Thanks!

If it can be assumed that there are not a bunch of missing receipts,
you could try this:

SELECT A.[Receipt#] + 1 as [Missing Receipt]
from tblSales as A
where not exists
( select 'true'
from tblSales
where [Receipt#] = A.[Receipt#] + 1 ) ;

However, it will only select the first of a missing sequence of
multiple missing receipts.
Further, there may be some missing receipts before the first one
found.

 

[Tara]

I must have done something wrong Lou. When I try to run the query, nothing
happens. Should the SQL statement look EXACTLY as you typed it?

Is there a way to find numbers that are missing from a group? For instance,
find numbers that don't follow sequence. We have a receipts database to
track sales of a fundraising item. I need to figure out which receipts are
missing from the group so that we know whether or not there are outstanding
receipts that our staff haven't turned in yet.

Thanks!

If it can be assumed that there are not a bunch of missing receipts,
you could try this:

SELECT A.[Receipt#] + 1 as [Missing Receipt]
from tblSales as A
where not exists
( select 'true'
from tblSales
where [Receipt#] = A.[Receipt#] + 1 ) ;

However, it will only select the first of a missing sequence of
multiple missing receipts.
Further, there may be some missing receipts before the first one
found.

 

[Lou]

I must have done something wrong Lou.  When I try to run the query, nothing
happens.  Should the SQL statement look EXACTLY as you typed it?

If it can be assumed that there are not a bunch of missing receipts,
you could try this:

SELECT A.[Receipt#] + 1 as [Missing Receipt]
from tblSales as A
where not exists
( select 'true'
from tblSales
where [Receipt#] = A.[Receipt#] + 1 ) ;

However, it will only select the first of a missing sequence of
multiple missing receipts.
Further, there may be some missing receipts before the first one
found.- Hide quoted text -

- Show quoted text -

This was a model of how I thought it could be done. You would need to
use your own table and column names.

My thinking was as follows:

Find those Receipt# for which there did not exist one in the next
numeric order.

I recognized that in the example of Receipt# 2,3,7,9 existing, the
query would only find 4 and 8 to be missing.