June 28, 2012 at 2:50 pm
I have a query that I'm trying to write for our HR dept.
They need to know when and who to send out awards for service every five, ten, 15,20,25, up to 45 years.
I need to take an employees dateofsenority (hire date) and figure out when they are due for an award. But the tricky part (at least for me) is if they have been an employee for lets say 20 years..I only want to return records that they are due that 20 year award not the awards for the 5,10 & 15 year mark (which they have already recieved). So I somehow need to filter out those results (max?)
Here's what I have so far...
SELECT
E.eecCoID AS 'Rec ID',
E.EecEEID AS 'Emp ID',
eepNameFirst AS 'First Name',
eepNameLast AS 'Last Name',
EecDateOfOriginalHire AS 'Service Date',
EepAddressLine1 AS 'Address 1',
EepAddressLine2 AS 'Address 2',
EepAddressCity AS 'City',
EepAddressState AS 'State',
EepAddressZipCode AS 'Zip',
CmpCompanyCode AS 'Co',
EecDateOfLastHire AS 'Service Date'
FROM
EmpPers
JOIN EmpComp E
ON E.eecEEID = eepEEID
JOIN Company
ON eecCoID = cmpCoID
WHERE
EecDateOfTermination IS NULL
AND EecDateOfSeniority
IN (SELECT (EC.EecDateOfSeniority)
FROM EMPCOMP EC
WHERE EC.EecEEID = E.EECEEID
AND eC.EecDateOfSeniority >= DATEADD(YEAR,DATEDIFF(YEAR,0,('12/30/2007')),0)
AND EC.EecDateOfSeniority <=DATEADD(YEAR,DATEDIFF(YEAR,0,('12/30/2007')),6)
AND e.EecDateOfSeniority >= DATEADD(YEAR,DATEDIFF(YEAR,0,('12/30/2007')),0)
AND E.EecDateOfSeniority <= DATEADD(YEAR,DATEDIFF(YEAR,0,('12/30/2007')),6))
I know my date fliters above are incorrect...really incorrect. How do I write this so that I'm bringing back only the employees due for their awards at the proper year mark?
I will scheduling this to run once a month.
June 28, 2012 at 3:10 pm
i think you just want the items where the modulous of year(getdate) - year(EecDateOfSeniority) is zero;
something like this:
with OneSample
AS
(
SELECT CONVERT(datetime,'12/30/2007') As EecDateOfSeniority UNION ALL
SELECT CONVERT(datetime,'12/30/2004') As EecDateOfSeniority UNION ALL
SELECT CONVERT(datetime,'12/30/2002') As EecDateOfSeniority UNION ALL
SELECT CONVERT(datetime,'12/30/2000') As EecDateOfSeniority UNION ALL
SELECT CONVERT(datetime,'12/30/1997') As EecDateOfSeniority UNION ALL
SELECT CONVERT(datetime,'12/30/1992') As EecDateOfSeniority UNION ALL
SELECT CONVERT(datetime,'12/30/1950') As EecDateOfSeniority
)
SELECT year(EecDateOfSeniority),
year(getdate()) - year(EecDateOfSeniority) As TotalYears
FROM OneSample
WHERE (year(getdate()) - year(EecDateOfSeniority)) % 5 = 0
Lowell
June 29, 2012 at 7:06 am
Thanks Lowell!
Your stuff is always so good. Wish I had read your reply before I started on what I wrote:
;with cte as (SELECT
E.eecCoID AS 'Rec ID',
E.EecEEID AS 'Emp ID',
eepNameFirst AS 'First Name',
eepNameLast AS 'Last Name',
EecDateOfOriginalHire AS 'Service Date',
EepAddressLine1 AS 'Address 1',
EepAddressLine2 AS 'Address 2',
EepAddressCity AS 'City',
EepAddressState AS 'State',
EepAddressZipCode AS 'Zip',
CmpCompanyCode AS 'Co',
EecDateOfLastHire AS 'Service Date1',
dateadd(year,5, EecDateOfLastHire) as [Due Date for 5 year of Service Award],
dateadd(year,10, EecDateOfLastHire) as [Due Date for 10 year of Service Award],
dateadd(year,25,EecDateOfLastHire) as [Due Date for 15 year of Service Award],
dateadd(year,20,EecDateOfLastHire) as [Due Date for 20 year of Service Award],
dateadd(year,25,EecDateOfLastHire) as [Due Date for 25 year of Service Award],
dateadd(year,30,EecDateOfLastHire) as [Due Date for 30 year of Service Award],
dateadd(year,35,EecDateOfLastHire) as [Due Date for 35 year of Service Award],
dateadd(year,40,EecDateOfLastHire) as [Due Date for 40 year of Service Award],
dateadd(year,45,EecDateOfLastHire) as [Due Date for 45 year of Service Award]
FROM
EmpPers
JOIN EmpComp E
ON E.eecEEID = eepEEID
JOIN Company
ON eecCoID = cmpCoID
WHERE
EecDateOfTermination IS NULL)
select *, [Due Date for 20 year of Service Award] as [Award Type]
from cte where [Due Date for 20 year of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
AND
[Due Date for 20 year of Service Award] < dateadd(month, 1+ datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
UNION ALL
select *, [Due Date for 10 year of Service Award] as [Award Type]
from cte where [Due Date for 10 year of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
AND
[Due Date for 10 year of Service Award] < dateadd(month, 1+ datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
UNION ALL
select *, [Due Date for 15 year of Service Award] as [Award Type]
from cte where [Due Date for 15 year of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
AND
[Due Date for 15 year of Service Award] < dateadd(month, 1+ datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
UNION ALL
select *, [Due Date for 5 year of Service Award] as [Award Type]
from cte where [Due Date for 5 year of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
AND
[Due Date for 5 year of Service Award] < dateadd(month, 1+ datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
UNION ALL
select *, [Due Date for 25 year of Service Award] as [Award Type]
from cte where [Due Date for 25 year of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
AND
[Due Date for 25 year of Service Award] < dateadd(month, 1+ datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
UNION ALL
select *, [Due Date for 30 year of Service Award] as [Award Type]
from cte where [Due Date for 30 year of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
AND
[Due Date for 30 year of Service Award] < dateadd(month, 1+ datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
UNION ALL
select *, [Due Date for 35 year of Service Award] as [Award Type]
from cte where [Due Date for 35 year of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
AND
[Due Date for 35 year of Service Award] < dateadd(month, 1+ datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
UNION ALL
select *, [Due Date for 40 year of Service Award] as [Award Type]
from cte where [Due Date for 40 year of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
AND
[Due Date for 40 year of Service Award] < dateadd(month, 1+ datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
UNION ALL
select *, [Due Date for 45 year of Service Award] as [Award Type]
from cte where [Due Date for 45 year of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
AND
[Due Date for 45 year of Service Award] < dateadd(month, 1+ datediff(month,'19000101',CURRENT_TIMESTAMP),'19000101')
the above works but..
what I need is to only send the records when an award is due...and not show the previous year awards..
In other words If someone has been with the company for 10 years I don't want to send them the five year award..only the ten year award.
With the above statement, I get columns for all the years, which seems like a great start but..
How can I just bring back the records that are needed?...filtering out all the not needed..
I really don't even need/want to see the 'Due Date for year xxxx' columns. I just need to do a calculation where I look at those values and only bring back the records for the appropriate date award. The award date that is closest to the current date.
This proc will be run once a month.
June 29, 2012 at 7:52 am
I figured it out..
thanks guys..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply