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!

 

[Chriss Miller]

I'd be interested, too.

Just one point is, this is not just any set not in another set, you look exactly for +1 of all the numbers you have and you also expect most successor values to exist. Plus it's a special comparison with two equally large sets, as apart from the shift +1 it's a self join.