Sql Query on one table sub query required

Question

Sql Query on one table sub query required

Trout

SSC Eights!

Points: 814
More actions
April 3, 2010 at 11:58 pm

#233263

Currently i have do a query by date on terminated employees that is fine. However, some employees after terminated come back and get a new employee number, I have to reconcile these terminations with access to the network.
At the moment i have been doing this in excel spreadsheet by matching up first and last name, as when employees return they get a new employee number not their old one.
so far my query is this. The employee table holds employeenumber, firstnames, lastname, commencedate, terminationdate, the termination date will be Null if the employee is active.
select EmployeeNumber,
Lastname,
case when charindex(' ', firstnames)>0 then substring (firstnames, 1, charindex (' ', firstnames)-1) else
firstnames end firstnames_1,
Firstnames,
CommenceDate,
TerminationDate
from
Employee
where terminationdate is not Null
and ....subquery.....
I would appreciate where to go on this, its the subquery that I am finding difficulty with.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What would be your expected output based on some sample data provided asdescribed in the first link in my signature? (Your current post doesn't really include a quesition...)

Side note: How are you going to deal with two persons having identical names being employed at the same time? Do you have any information that a person returned and what emp# he had before?

How will you deal with duplicate results? E.g. You have two emps "John Smith" employed at the same time. Both are leaving the company. One returns. Which emp# do you assign? And based on what rule?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

No need for an inline view a.k.a. subquery.

Just joing Employee table with itself - use different aliases.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Trout SSC Eights! Points: 814 More actions · Answer 3

hi.

what would solve all this is create a field in payroll which is unique and link it to the active directory account but my IT Manager will not have it. I believe the application has the ability to do this.

With regards to two john smiths, we would have a different location but they could also work at the same location, this has not happened yet, so i have no answer on this.

I am stuck with firstname and lastname matching.. this is not ideal... but Payroll insist on giving any returning employees a new employee number, I might ask them if they are a returning employee on the record somewhere create a field to record the former employee number if they did that then I would have something....I might suggest that to them... I will try that first before doing anything.

thanks..