Re: Calling all math wizards

Very interesting problem, Paul.
I presume the number has to round-trip, either mathematically or with a x-ref table? Establishing a table with a one-to-one relationship is fairly straight forward so I will ignore that choice of solution as trivial and outside of your needs.

How sparse is the table? More than 10 companies? More than 99 divisions in any one company? More than 9999 employees at any single division?
My first shot would be to consider some other field, too, as part of the new key. Maybe the Division Number catted to the last 4 numbers of the employee's SSN? Or, the last digit of the Company Number, the last two digits of the division number, and the last four digits of the SSN?

The last 4-digits of the SSN seems quite useful, at least for a lot of other kinds of identification validations. How to couple it with portions of the existing number may be obvious, once you look at the sparseness of the table matrix?

But in the end, if the table is not sparse then the solution has to be some other table, In my opinion.


Paul Nelson wrote:

A client has posed an interesting problem. His employee master file contains
a 2 digit company number, a 3 digit division number, and a 5 digit employee
number. He needs to have some math done to generate a unique 7 digit number.

I've thought about using random numbers, square roots, logarithms, etc., but
this has to tie back into his application software, and the number has to be
sent out to a third party employee benefits administrator so duplicates are
not permitted.

I could come up with something via RPG, but the client's desire is to be
able to do this via SQL. Any thoughts?

Paul Nelson
Cell 708-670-6978
Office 512-392-2577
nelsonp@xxxxxxxxxxxxx