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".