asked on

SQL 2005 Date Query Question

I'm trying to find employee that are coming up on their company anniversary date. The data is in a SQL 2005 table. What I'm trying to do is find employee's who have been working at the company for 5, 10, 15, 20, 25 year increments FROM hire_date TO 11:59:59pm of 12/31/CurrentYear. I currently grab all employees (for a given month or year) and have my ColdFusion code parse the output to give me the results I need. However, I wouldn't think that's the most efficient way of doing it. Is there a way do this the SQL query?

What I have now is:
<cfquery name="myqry" datasource="#Main_DataSource#">
select last_name, first_name, empl_id, orig_hire_dt, s_empl_status_cd
from empl
where Month(orig_hire_dt) = #Month(mydate)#
and s_empl_status_cd = 'act'
order by #mysort# asc
</cfquery>
This query may grab 50 employees for the month January, and through my CFOUTPUT using a DATEDIFF procedure, parse the output down this:
Joe Schmo - 5 years
Suzy Que - 10 years
Billy Bob - 25 years

Like I said, the query grabs 50 records when I'm really only interested in 3. Is there a way do this in the SQL (perhaps using multiple DATEDIFF statements)? I'm stumped.. Thanks!

tigin44

something like this may help you

select  last_name, first_name, empl_id, orig_hire_dt, s_empl_status_cd,
    (datediff(year, orig_hire_dt, getdate ) % 5) as hire_duration
from    empl
where  Month(orig_hire_dt) = @Month(mydate)
  and  (datediff(year, orig_hire_dt, getdate ) % 5)  = 0
and  s_empl_status_cd = 'act'

Open in new window

Muhammad Ousama Ghazali

Try to use the following query which assumed a table named Employees and any number of fields containing one field named HireDate of type datetime.

SELECT E.*, (DATEDIFF(mm, HireDate, ('' + CONVERT(VARCHAR, YEAR(GETDATE())) + '-12-31 23:23:59')) / 12) AS NoOfYears 
FROM Employees AS E
WHERE ((DATEDIFF(mm, HireDate, ('' + CONVERT(VARCHAR, YEAR(GETDATE())) + '-12-31 23:23:59')) / 12) % 5) = 0

Open in new window

egypt123

ASKER

This certainly gets me very close to what I need. However, it's also returning records for employees who have 0 (zero) years of service. Is there way to ignore or not grab those (0 years) records and only grab 5, 10, 15, etc... ?? I can do it with my CF parsing, but that means I'm basically back to what I had (just slightly less code). I appreciate the quick reply.

tigin44

SELECT E.*, (DATEDIFF(mm, HireDate, ('' + CONVERT(VARCHAR, YEAR(GETDATE())) + '-12-31 23:23:59')) / 12) AS NoOfYears
FROM Employees AS E
WHERE ((DATEDIFF(mm, HireDate, ('' + CONVERT(VARCHAR, YEAR(GETDATE())) + '-12-31 23:23:59')) / 12) % 5) = 0
and (DATEDIFF(mm, HireDate, ('' + CONVERT(VARCHAR, YEAR(GETDATE())) + '-12-31 23:23:59')) / 12) > 0

ASKER CERTIFIED SOLUTION

Muhammad Ousama Ghazali

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

egypt123

ASKER

Thanks!

egypt123

ASKER

Thanks, folks! Everyone's soultion I think worked in some form or fashion, but moghzail's got me to where I need to be the soonest so I gave him the points.

Muhammad Ousama Ghazali

The '' + part within EndDate part of the DATEDIFF function in the query is not needed, so you can delete it without no problems. Use the code below:

SELECT E.*, (DATEDIFF(mm, HireDate, (CONVERT(VARCHAR, YEAR(GETDATE())) + '-12-31 23:23:59')) / 12) AS NoOfYears 
FROM Employees AS E
WHERE ((DATEDIFF(mm, HireDate, (CONVERT(VARCHAR, YEAR(GETDATE())) + '-12-31 23:23:59')) / 12) % 5) = 0
AND (DATEDIFF(mm, HireDate, (CONVERT(VARCHAR, YEAR(GETDATE())) + '-12-31 23:23:59')) / 12) <> 0

Open in new window