EmployeeNumber query in SQL Server 2008
I am trying to write a query that will generate employee numbers. Employee number = first initial & first 7 characters of last name. UNLESS that is taken, then it's first initial, first 6 characters of last name, then the number 1. UNLESS that is taken, then it's first initial, first 6 characters of last name, then the number 2... UNLESS... until we get to the number 9 and give up... HOW?
Software/Hardware used:
SQL Server 2008
Software/Hardware used:
SQL Server 2008
ASKED:
June 20, 2011 7:25 PM
UPDATED:
June 21, 2011 7:01 PM
Answer Wiki:
Since the list is finite (1..9), you can do this with a single query:
<pre>declare @sLastName varchar(255) (e.g., "Smith")
declare @sFirstName varchar(255) (e.g., "John")
declare @sFLname varchar(255)
declare @sFLnameX varchar(255)
set @sFLname = left(@sFirstName,1) + left(@sLastName, 7);
set @sFLnameX = left(@sFLname, 7);
select top 1 tmp.LogonID
from
(
select 1 as OrderByCol, @sFLname as LogonID
where not exists (select 1 from ExistingLoginsTable where LoginID = @sFLname)
union all
select 2 as OrderByCol, @sFLnameX + '1' as LogonID
where not exists (select 1 from ExistingLoginsTable where LoginID = @sFLname + '1')
union all
select 3 as OrderByCol, @sFLnameX + '2' as LogonID
where not exists (select 1 from ExistingLoginsTable where LoginID = @sFLname + '2')
...
union all
select 9 as OrderByCol, @sFLnameX + '9' as LogonID
where not exists (select 1 from ExistingLoginsTable where LoginID = @sFLname + '9')
) tmp
order by tmp.OrderByCol;</pre>
Not exactly elegant, but it works...
The query will return the first login id that has not been used, or null if all candidate ids have been used.
To see all answers submitted to the Answer Wiki: View Answer History.
You will probably need to do this in a stored procedure, with some kind of loop.
But, this looks like a homework assignment, so I think you are not going to get the full answer here.
I wish to God it was. I am a 50 year old consultant, and, part of the system I support uses SQL server. Obviously, I am not a DBA, I just get ‘stuck’ doing it once in a while… SO, now, how do I prove I am not a student???
How about this? I need this as part of supporting Courion. Search the internet for Courion Tip and see if this convinces you…You should come to my website, http://mmdmurphy.wordpress.com…
You’ll need either a function or stored procedure to handle this work. That’s a damn crappy with of generating employee numbers.
Why have you decided to devise the key in this way?
Is there a reason for only 8 characters?
There are to many variables to encode. How many John Smiths are there in the world, and say 10 of them need encoding and how would you code that using your method?
Using things like departments, names, job titles or anything that could be duplicated is not an ideal way to create unique keys.
I have used a method,which incorporating the 5 digit date code within the make up of the key. By adding a suffix or prefix, it allows for many records being created on the same day.
I didn’t make the mess, I inherited it. As far as what the employee number is, its not really employee number – its a user’s SAP logon user name. And so they store a user’s SAP Logon ID in Active Directory as Employee Number. Why is it only 8 characters? I don’t know SAP enough to answer. And, just to confuse things, every employee has an employee ID that IS unique. So, if they would logon to SAP with that, I wouldnt have an issue.
At some point we are going to automate SAP account creation, based on these employee numbers.