June 1, 2012 at 9:29 am
I have this HR query that I'm having some trouble with.
It is used to pull the count of number of terminated employees. But the problem is some employees get hired back. They don't want to see any active employees (status = 'A').
SELECT
CmpCompanyCode,
cmpCompanyName,
E.eecCoID,
E.EecEEID, --unique number stays with person, if listed more then once
E.EecOrgLvl2,
E.eecDateOfLastHire,
E.eecDateOfTermination,
E.eecEmplStatus,
E.eecEmpNo,
E.eecLocation,
E.eecFullTimeorPartTime,
E.eecJobCode,
eepNameLast,
eepNameSuffix,
eepNameFirst,
EepNameMiddle
FROM EmpPers JOIN EmpComp E ON E.eecEEID = eepEEID
JOIN Company ON eecCoID = cmpCoID
WHERE EecDateOfTermination IS NOT NULL
The above code works fine. My problem is that some employees get hired back. So, I need to loop through all E.EecEEID records to check for duplicates and if there are duplicates for a E.EexEEID record then look at the E.eecEmplStatus field to see if that value = 'A' (for active) for that employee.
If at anytime the employee is active ('A') that means that even tho we terminated them once or twice , they are now an employeee again, so do not bring back the record.
CTE...temp table?..I don't know how to loop thru and then filter out if they are active.
June 1, 2012 at 9:36 am
Wouldn't this work?
SELECT
CmpCompanyCode,
cmpCompanyName,
E.eecCoID,
E.EecEEID, --unique number stays with person, if listed more then once
E.EecOrgLvl2,
E.eecDateOfLastHire,
E.eecDateOfTermination,
E.eecEmplStatus,
E.eecEmpNo,
E.eecLocation,
E.eecFullTimeorPartTime,
E.eecJobCode,
eepNameLast,
eepNameSuffix,
eepNameFirst,
EepNameMiddle
FROM EmpPers JOIN EmpComp E ON E.eecEEID = eepEEID
JOIN Company ON eecCoID = cmpCoID
WHERE EecDateOfTermination IS NOT NULL and E.eecEmplStatus <> 'A'
June 1, 2012 at 9:41 am
Or maybe this:
SELECT
CmpCompanyCode,
cmpCompanyName,
E.eecCoID,
E.EecEEID, --unique number stays with person, if listed more then once
E.EecOrgLvl2,
E.eecDateOfLastHire,
E.eecDateOfTermination,
E.eecEmplStatus,
E.eecEmpNo,
E.eecLocation,
E.eecFullTimeorPartTime,
E.eecJobCode,
eepNameLast,
eepNameSuffix,
eepNameFirst,
EepNameMiddle
FROM EmpPers JOIN EmpComp E ON E.eecEEID = eepEEID
JOIN Company ON eecCoID = cmpCoID
WHERE EecDateOfTermination IS NOT NULL
and not exists (select 1 from EmpComp e2 where e2.EecEEID = E.EecEEID and e2.E.eecEmplStatus = 'A')
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 1, 2012 at 9:55 am
Thanks Phil & Lynn...
you both have helped me before..just wanted to thank you both so much.
good karma to you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply