List Out Missing Numbers of a Number Range

[dylim]

Hi Guys,

Me again. I need to show the user which invoice numbers are missing given a number range.

Suppose the following:

Actual Invoice Numbers:   1,2,3,4,5,8,9,11,12,13,15   (each of these numbers are in their respective invoice records)
Number Range: 1 to 15                                 (passed as a parameter, 2 values)

Missing numbers should be: 6,7,10,14

My knee jerk algorithm would be to:

1. Execute a SELECT statement to get the actual numbers within the range into a cursor.
2. LOOP thru the Number Range and do a SEEK or LOCATE against the Actual Invoice Numbers.

Perhaps the experts can chime in on this?

Thanks in advance. Long Live the Fox!

 

[mjcmkrsr]

Hi,

Please find below a sketch of code. Feel free to adapt to your needs.

LOCAL ARRAY laAllValues[1]

CREATE CURSOR csrValues (iValue I)

FOR i = 1 TO 25
    INSERT INTO csrValues VALUES (INT(RAND() * 50))
ENDFOR

SELECT MAX(iValue), MIN(iValue) FROM csrValues INTO ARRAY laAllValues

CREATE cursor csrAllValues (iValue I)

FOR i = 0 TO laAllValues[1] - laAllValues[2]
    INSERT INTO csrAllValues VALUES (laAllValues[2] + i)

ENDFOR

SELECT * FROM csrAllValues WHERE iValue NOT in (Select iValue FROM csrValues) ORDER BY 1 INTO CURSOR csrMissingValues

BROWSE

CLOSE ALL

RETURN

hth

MarK

 

[dylim]

Hi Mark,

SELECT * FROM csrAllValues WHERE iValue NOT in (Select iValue FROM csrValues) ORDER BY 1 INTO CURSOR csrMissingValues

So, basically, create a cursor for "all values" (csrAllValues), then check against csrValues.

Nice! Thanks you sir!

Will compare the LOOP / LOCATE algo against this one for performance.

 

[Tore Bleken]

From my session at SoutWest Fox last year:

Find gaps in asequence
Background: You have a table which is supposed to contain a series of sequential data, for instance a list of invoice or order numbers. If you find or suspect that some records are missing or contains doubled values, this code will find them:
Select field from table where field+1 not in (select field from table)
This is a more general version, where pass the table name and field name as parameters.For demonstration purposes I have added default values.

*findgap.prg
Lparameters tcTable,tcFieldLocal lcSQL
m.tcTable = Evl(m.tcTable, [data\orders])m.tcField = Evl(m.tcField, [orderid])Text To m.lcSQL textmerge Noshow Pretext 3
Select << m.tcField >> + 1 as missing from << m.tcTable >>where << m.tcField >> + 1 not in
(Select << m.tcField >> from << m.tcTable >>)EndText
*!*  _cliptext = m.lcSQLExecScript(m.lcSQL

 

[mjcmkrsr]

Hi

So, basically, create a cursor for "all values" (csrAllValues), then check against csrValues.

Yes - ALL values in the chosen range. In your case laAllValues[1] = 15 and laAllValues[2] = 1

hth

MarK

 

[dylim]

From my session at SoutWest Fox last year:

Find gaps in asequence
Background: You have a table which is supposed to contain a series of sequential data, for instance a list of invoice or order numbers. If you find or suspect that some records are missing or contains doubled values, this code will find them:
Select field from table where field+1 not in (select field from table)
This is a more general version, where pass the table name and field name as parameters.For demonstration purposes I have added default values.

*findgap.prg
Lparameters tcTable,tcFieldLocal lcSQL
m.tcTable = Evl(m.tcTable, [data\orders])m.tcField = Evl(m.tcField, [orderid])Text To m.lcSQL textmerge Noshow Pretext 3
Select << m.tcField >> + 1 as missing from << m.tcTable >>where << m.tcField >> + 1 not in
(Select << m.tcField >> from << m.tcTable >>)EndText
*!*  _cliptext = m.lcSQLExecScript(m.lcSQL

This is cool. Will check this out too. Thanks Tore!

 

[Tore Bleken]

Just to avoid any misunderstandings; my code won't find every missing number, but it will find every first record where there are "holes".

 

[dylim]

Just to avoid any misunderstandings; my code won't find every missing number, but it will find every first record where there are "holes".

This is confirmed Sir Tore.

Code I used (thanks Tore):

select nrefno+1 as missing from serial where nrefno+1 not in (select nrefno from serial )

It will detect the first "hole", it will not show if there is a second consecutive missing one.

The desired query result should have been 4, 8, 10, 13 and 14.

 

[Joe Crescenzi]

So, basically, create a cursor for "all values" (csrAllValues), then check against csrValues.

My two cent...

If this is something you will do often, you can improve the performance by creating a permanent table instead of a temporary cursor so it doesn't have to keep creating it and deleting it on the fly.

The other advantage is that you can also place an index on the serial column.

Just be sure the permanent table covers every possible value, including future values.

 

[dylim]

My two cent...

If this is something you will do often, you can improve the performance by creating a permanent table instead of a temporary cursor so it doesn't have to keep creating it and deleting it on the fly.

The other advantage is that you can also place an index on the serial column.

Just be sure the permanent table covers every possible value, including future values.

Hi Joe,

Agree with you on this in that a permanent table will be provide for a faster and snappier operation.

However, this will be an ad hoc type of query, wherein the management checks for any missing invoices that were not posted. So basically, management will query the system for any missing invoice numbers within a range of numbers supplied by the audit manager. Hence, no choice but to make it on the fly sir.

Thanks for your inputs.

 

[Joe Crescenzi]

Hi Joe,

Agree with you on this in that a permanent table will be provide for a faster and snappier operation.

However, this will be an ad hoc type of query, wherein the management checks for any missing invoices that were not posted. So basically, management will query the system for any missing invoice numbers within a range of numbers supplied by the audit manager. Hence, no choice but to make it on the fly sir.

Thanks for your inputs.

Performance for reporting is subjective based on how often you need to run the report,

Keep in mind that even when the range varies, a permanent table can still be used because although I didn't mention it, you would be using a range in your query so it's still optimized for current and future values in any range.

For example:

SELECT * ;
   FROM AllValues ;
WHERE BETWEEN(iValue, iMinValue, iMaxValue);
     AND iValue NOT in (Select iValue FROM csrValues)

Technically, you can also use a range in the second part of the query too...

SELECT * ;
   FROM AllValues ;
WHERE BETWEEN(iValue, iMinValue, iMaxValue);
     AND iValue NOT in (Select iValue FROM csrValues WHERE BETWEEN(iValue, iMinValue, iMaxValue) )

 

[Chriss Miller]

Assuming your Invoice table is Invoices.dbf and the invoice field and index tag both are named Invoice, then this will do it best:

Create Cursor Gaps (iGapStart I, iGapEnd I)
*Insert Into Gaps Values (0,0) && optional for catching the gap of Invoices from 1 to Min(Invoice)-1, when Min(Invoice)>1

Use Invoices In 0 Order Invoice
Use Invoices In 0 Again Alias CheckNext Order Invoice
Select CheckNext
Set Relation To Invoice+1 into Invoices
Scan For Eof("Invoices")
   Insert into Gaps (iGapStart) Values (CheckNext.Invoice+1)
EndScan

Set Near On
Select Gaps
Scan
   =Seek(Missing.iGapStart,"Invoices","Invoice")
   If !Eof("Invoices")
      Replace iGapEnd With Invoices.Invoice-1
   Else
      Delete In Gaps
   Endif
EndScan
Set Near Off
Go Top
Browse

 

[dylim]

Assuming your Invoice table is Invoices.dbf and the invoide field and index tag both are named Invoice, then this will do it best:

Create Cursor Gaps (iGapStart I, iGapEnd I)
*Insert Into Gaps Values (0,0) && optional for catching the gap of Invoices from 1 to Min(Invoice)-1, when Min(Invoice)>1

Use Invoices In 0 Order Invoice
Use Invoices In 0 Again Alias CheckNext Order Invoice
Select CheckNext
Set Relation To Invoice+1 into Invoices
Scan For Eof("Invoices")
   Insert into Gaps (iGapStart) Values (CheckNext.Invoice+1)
EndScan

Set Near On
Select Gaps
Scan
   =Seek(Missing.iGapStart,"Invoices","Invoice")
   If !Eof("Invoices")
      Replace iGapEnd With Invoices.Invoice-1
   Else
      Delete In Gaps
   Endif
EndScan
Set Near Off
Go Top
Browse

Chriss,

Will try this out sir. So basically, it is using "old-fashioned VFP commands" vs "SQL commands".

Thanks as always.

 

[Chriss Miller]

So basically, it is using "old-fashioned VFP commands" vs "SQL commands".

Yes, in this case it would be very omissive to not make use of RELATION to the next number as a very nicely working concept.

Indeed the second part could also be done with workareas ordered descending and a relation to Invoice-1 instead of Invoice+1. Because that finds all gap ends and you then can combine starts and ends.

You could also use the same principle in an SQL JOIN to the next or previous invoice number instead of the typical joining on matches of same numbers or ids.

I personally also like SQL, I just don't trust the VFP SQL engine to optimally do the NOT IN (SELECT field FROM table) construct, in this special case. Also you don't need a tally table in this case, the table compared to itself shifted by one record shows its gaps and that means comparisons of neighbouring records instead of the full sets. In the end you can try both ways with realistic data in a realistic size and see what works better.