- Jan 15, 2001
- 15,069
- 94
- 91
The project numbers for our internal tracking system are of this format:
YY-XXXX-NX
YY = the last two digits of the year
XXXX = incrementing number from 0000 to 9999
NX = job type (P, L, N, D, etc.) + job number (0-9)
The entire thing is unique, but you won't know the NX part until sometime later, so it actually isn't important because the first 6 digits are also unique. However, the XXXX part needs to reset every year. Normally I do this with an autoincrement field because then it's easy: insert in a transaction and read the id back out to guarantee the intended id is returned. Can I reset the column on the first insert every year? Is there a better way to do this?
Ultimately, this is what I want:
ProjectNumber = GetProjectNumber()
Where project number is a string that is always unique and resets at the beginning of each year.
YY-XXXX-NX
YY = the last two digits of the year
XXXX = incrementing number from 0000 to 9999
NX = job type (P, L, N, D, etc.) + job number (0-9)
The entire thing is unique, but you won't know the NX part until sometime later, so it actually isn't important because the first 6 digits are also unique. However, the XXXX part needs to reset every year. Normally I do this with an autoincrement field because then it's easy: insert in a transaction and read the id back out to guarantee the intended id is returned. Can I reset the column on the first insert every year? Is there a better way to do this?
Ultimately, this is what I want:
ProjectNumber = GetProjectNumber()
Where project number is a string that is always unique and resets at the beginning of each year.