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.

 

[mmerlinn]

Oh, I thought you were accusing me of nitpicking your code, while I was having questions about almost all lines of my code in conjunction with legacy FoxPro, blame me. You don't want to dig into it, okay. It's just a pitty your code is so brute force, that it hurts. I'm used far better code from your FAQs. Please take the compliment out of that rather than the criticism. If you're fine with your own function now, you are. As simple as that.

Nope, not accusing you of anything. Once I saw parts of your code that will not work for me, I quit examining your code. Compared to most code I see posted, yours looks a lot like plain jane FP code, so with a little time I could probably modify it to work in FP 2.6.

Sometimes when I see VFP code my eyes blur over because what I tend to see is code bloat. Since I have never been able to migrate to VFP, trying to understand exactly what is happening is often overwhelming. I just watch, then jump in when there is something I can contribute.

Obviously, VFP must be better and easier to use, else no one would have migrated voluntarily. It just does not seem that way to me. And for me, VFP will never be an option.

As far as the code I wrote and posted, it definitely is brute force. I prefer simple concise solutions that never fail. I did a quick and dirty from a different angle, then massaged it into something that could be used. Will I ever use it? No idea. But it goes into my code closet just in case. And since it works, although probably slower than other solutions, no big deal outside of loops, I do not see any reason to do any more tinkering with it. I am sure with a little thought, and study of other solutions, I could come up with something better and/or faster. Not a one liner for sure, although that would tickle me pink if I could find a way.

And, by the way, the qOutType parameter check I did in the code I posted is some of the worst code I have written. I did everything backwards there, opposite of what I normally do. I caught the insanity before I filed the function away for future use, but was unable to edit the post to change those 3 lines of code.

My coding style is the result of years of Apple BASIC coding on a 16k, 1mhz machine. Variable uniqueness maxed out at 2 characters, every single byte counted, and garbage collection was a fact of life, forcing me to code outside of the box, often doing things that others said were impossible. They say recursion is impossible in Apple BASIC, but I found a way to do it when I crunched a 250-line shell sort program down to 3 lines of code while at the same time speeding it up, i.e. a 12 minute sort wound up being a 12 second sort.

Using an Apple II+, I published over 200 inhouse technical reference booklets (10-30 pages each), all different, all formatted and published using Apple BASIC, some of which I still use. I also published 2 technical reference manuals (8" x 11" x 200+ pages) that I sold for $100 each in the early and mid 90s using that machine.

 

[Chriss Miller]

Thank you for that kind and detailed feedback.

Re the brute force code: Be aware that a) you will be recognized as a big mind in FoxPro if anyone just looks into the FAQ section of this forum. And b) beginners like dylim will therefore take for granted your code will be fine, maybe prefer it to something that's perhaps less easy to understand but would be better. That's therefore not only a choice you make for yourself and you take a bit too low responsibility when not pointing out that features. You already indirectly indicated you don't care for that solution very much, as you said while there is no possibility for larger arrays in legacy pure FP, you would then do it chunkwise in 64k steps. It's still a bit disappointing, but then I see you come down to be simple yet precise and you do care for optimality in other aspects.

Let me respond to that by pointing out how much work the RELATION does by just setting it once for the whole loop. I actually think you know, as that also exists in old FP, but you don't seem to understand what big performance impact it has. Everytime you change record in a workarea with a relation going off from it, that relation fires without even needing to write a SEEK for that to happen, so you do a lot of seeks without the VFP runtime to interpret the byte code of a SEEK at all. It's just automatic. As a side note: That's also the thing that works out with SQL, a query is just once parsed and then the plan executes throughout the sets involved. That's all missing from old FP.

To go one step deeper, the SCAN FOR EOF() challenges the RELATION as many times as needed - maybe the runtime code for the RELATION will even do this in one sweep, unfortunately I don't have hands on the implementation of it - to find the next gap, where the relation does not find a next value. That's making the SCAN loop only iterate over gap starts.

The other main thought about it is that a gap has a start and end, whereas obviously though naturally by probability of gaps in such numbers dylim only cares for the single gap values, if there is a streak as the 6,7 is in his sample data it will simply mean more gap numbers. If you would have a gap of 1000 numbers - maybe intentionally put in - I find it obtuse to list them all one by one. Thinking about gaps as ranges, even if most of them have start=end, you also just see what Tore saw, that one pass of looking for value+1 only finds starts. The way NEAR helps here is finding the ends of gaps, and in case of the wrong gap starting after the last record, you will not find an end of that gap and identify it as "wrong" - depends on how you interpret it, but clearly wrong for the outset. That even takes less than to first care for finding the max value to not look beyond it. I spare to care for the extreme cases as the whole iteration will catch it anyway. Which is also true for the 0 to min-1 gap nobody cared about. Well, okay, you need to put in the effort of adding a 0,0 record as seed data. In the extreme you could start with -2^31 and go up to 2^31-1 as max and list -2^31 to min-1 and the max+1 up to integer max value as the "gaps" before and after all data. It's working straight forward.