I'm working with a table from a in-house development POS system. This table is just a list of line items transactions without a way to relate records. I would like to synthesizing a Invoice Number for each record.
The available fields are:
Serial Number (unique)
Customer ID (unique)
My first thought was to synthesize a Invoice Number based on the Purchased Date and Customer ID. Basically I added the values together:
Invoice Number = Customer ID + Year ( Purchased Date ) + Month ( Purchased Date ) + Day ( Purchased Date )
I was feeling smug until further review I noticed the Invoice Numbers wasn't unique for Purchased Date + Customer ID calculation.
Invoice Number 66481 = 1+7+2015 + 66458
Invoice Number 66481 = 2+13+2015 + 66451
Invoice Number 66481 = 3+27+2015 + 66436
Back to the drawing board. Hopefully someone with better understand can point me in the right direction.