EmployeeNumber query in SQL Server 2008

30 pts.
Tags:
IF logic
IF statement
SQL Query
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
ASKED: June 20, 2011  7:25 PM
UPDATED: June 21, 2011  7:01 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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.
    65,110 pointsBadges:
    report
  • Mmdmurphy
    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...
    30 pointsBadges:
    report
  • Denny Cherry
    You'll need either a function or stored procedure to handle this work. That's a damn crappy with of generating employee numbers.
    65,490 pointsBadges:
    report
  • Chippy088
    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.
    4,625 pointsBadges:
    report
  • Mmdmurphy
    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.
    30 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following