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
where Month(orig_hire_dt) = #Month(mydate)#
and s_empl_status_cd = 'act'
order by #mysort# asc
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!