I have a client that wants me to generate employee numbers. I cannot figure out how. I have a table of firstname, lastname, and employeenumber.
The logic is this:
employeenumber = firstinitial & first 7 characters of lastname --- IF that's not taken. If it is...
employeenumber = firstinitial & first 6 characters of lastname & '1'-- IF that's not taken. If it is...
employeenumber = firstinitial & first 6 characters of lastname & '2'-- IF that's not taken. If it is, you get the idea up to 9. Then give up (though we might need to go to some higher number... might need to go to first initial & first 5 characters & then go double digits)
This is used as a logon for an older system that only accepts 8 character user names, which have to be unique... So, I would probably be dmurph9
asked Jun 20 '11 at 12:51 PM in Default
Here is a version that should handle more than ten ties in terms of shortened names. This means it will automatically reduce the alphanumeric name length and increment the ID added to the end of login name for names that are the same.
This is not perfect, if you have too many ties, then the LEFT() command will crash. We're talking 8 figures here though, so I think that would be rare!
What I did was to take the initial shortnames that are valid (8 characters, as specced). Then run a ROW_NUMBER() identifying the shortnames that are the same, this is all in the CTE shortnames.
I then ran a CASE statement, finding shortnames that were long enough and had ties. These were then modified to take the necessary amount of letters in the short column and decide how many letters to throw away and replace with a digit. This automatically works regardless of how many ties you have, as displayed in my example code, with SLongname being changed up to SLongn10.
EDIT 3: Missed the case of usernames that are shorter than 8 but still tie, that is in there now.
EDIT 4: Changed the final CASE to fit the spec better - thanks @Oleg :o)
EDIT 5: Last change to fix the brokenness as found by @Kevin Feasel. This should fulfill all requirements.
Update: I have added in a way to get past the 11-match barrier. The downside is that you do have "lost" numbers, but unless the company hires hundreds of people with the same first initial and most of the last name, it should be fine... I also extended the test data to become more interesting, so now there are two sets of names which pass the eleven-match barrier: C. Smith and C. Smithe*. I still recommend doing some more testing, but I'm more confident about this than I was earlier on today when I posted the first version.
The short version of the change is that I'm now looking at the row_number for the first five characters of the last name, and in the event that the 6-character return is at least 10, return the 5-character row number. This results in some numbers being skipped (there is no CSmith10 or CSmith11 in this case, nor 17-25), but should guarantee that each username is unique. To go up to three digits, rinse and repeat with four-character codes, though that's getting to be ridiculous.
This is for SQL 2005 or later, as it uses ROW_NUMBER() and a common table expression.
Some of the things I did--like LastName7 and LastName6--weren't absolutely necessary, but I already needed a common table expression to get the row number, so I figured I would create friendlier aliases.Finally, you might need to tweak it a little bit if you don't have an added time or some other marker to show, historically, what was already done. You could probably even use an identity column and insert your seed data (the employee records you already have) in ascending employee number order. Then, instead of ordering by the added time, you would order by the identity column.