Re: Pulling Random Records from a file

["Buck" <buck.calabro@xxxxxxxxxxxx>]

John said:

> Wanted to get some feedback about possible options to pull random records
> from a file.   I am dealing with an invoice file for a date (one day) for
> multiple locations.  I need to pull thirty random bills from this file for
> a date and the number of bills I pull for a location must be proportionate
> to that location's number of bills compared to number of bill's for all
> locations.

Are you looking for an even distribution of 'randomness' by time, invoice
number, customer service rep or something else?  I'm not trying to be an
idiot (it comes naturally) but it seems to me that the order entry process
randomises things quite a bit, and the first n invoices for each location
should do the trick for a QA sort of thing.

Let's say you want a random distribution by invoice number.  You need to
find the high and low invoice numbers, and a count of invoices for each
location (may not be the difference between high and low if invoices can be
voided or otherwise unaccounted for in this file.)  Now that you have those
numbers, you can figure out how many invoices you want to pull for this
location (n).  You need to decide if you will always start with the first
invoice for that location, or somewhere in the middle of the pack.  If in
the middle, generate a random number between 1 and (invoices/n).  Add that
number to the lowest invoice number for that location and you have your
first invoice (setll).  Now you can pull n-1 more evenly spaced invoices by
dividing the total number of invoices by n-1.

Example:
Lowest invoice number 1
Highest invoice number 530
Invoices at location 525
Number to pull 12
Random number between 1 and 43  3
First invoice 1+3 = 4
Invoice spacing 525/12 = 43
Next invoice 4+43 = 47
Next 47+43 = 90
Next 90+43 = 133
etc.

This method will pull 12 invoices spaced roughly 43 invoice numbers apart
beginning with a random invoice somewhere in the first 43.

One idea to start the conversation.
  --buck