Re: Calling all math wizards

[Booth Martin <booth@xxxxxxxxxxxx>]

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