October 2, 2012 at 10:46 am
I have this query ..works great but now I need to exclude employees that have worked here less than four years, based on 'eecdateoforigianl hire.
SELECT
'WAS3' AS 'Rec ID',
E.EecEEID AS 'Emp ID',
eepNameFirst AS 'First Name',
eepNameLast AS 'Last Name',
EecDateOfOriginalHire AS 'Service Date',
(SELECT DATEDIFF(YEAR, EecDateOfOriginalHire, getdate()) from empcomp EC WHERE EC.EecEEID = E.EECEEID) as 'Yrs of Serv'
FROM
EmpPers
JOIN EmpComp E
ON E.eecEEID = eepEEID
JOIN Company
ON eecCoID = cmpCoID
WHERE
EecDateOfTermination IS NOT NULL
AND EXISTS
(SELECT 1
FROM EmpComp e2
WHERE e2.EecEEID = E.EecEEID
--AND e2.eecEmplStatus <> 'A')-- changed to <> ...this WAS/is to filter out anyone that was termed then re-hired
--AND E.EecTermReason NOT IN ('I01','I02','I03','I14','I22','V05','V07','V09','V12','V22','V13', 'TRO')
AND E.eecDateOfTermination
IN (SELECT (EC.eecDateOfTermination)
FROM EMPCOMP EC
WHERE EC.EecEEID = E.EECEEID
AND EC.eecDateOfTermination IS NOT NULL
AND eC.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND EC.eecDateOfTermination <=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
AND e.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND E.eecDateOfTermination <= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)))
--AND eC.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,('12/30/2007')),0)
--AND EC.eecDateOfTermination <=DATEADD(wk,DATEDIFF(wk,0,('12/30/2007')),6)
--AND e.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,('12/30/2007')),0)
--AND E.eecDateOfTermination <= DATEADD(wk,DATEDIFF(wk,0,('12/30/2007')),6)
--AND E.eecDateOfTermination IS NOT NULL ))
I don't know how to exclude these employees.
October 2, 2012 at 11:51 am
datediff(yy, date1, date2) > 4
If you want some more detailed help you are going to have to provide something to work with. With over 600 points you are by no means new around here and know that we need ddl and sample data.
FWIW, I think you could probably do this entire thing in a single select statement without all the various subselects. The entire EXISTS portion is doing a self join from empcomp to itself not once but twice as subselects. All of this could be rolled into the original join. There really is no need for all the extra work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 2, 2012 at 12:19 pm
Okay thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply