Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

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!
0
egypt123
Asked:
egypt123
  • 3
  • 3
  • 2
1 Solution
 
tigin44Commented:
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

0
 
Muhammad Ousama GhazaliSolution Analyst & ArchitectCommented:
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

0
 
egypt123Author Commented:
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
tigin44Commented:
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
0
 
Muhammad Ousama GhazaliSolution Analyst & ArchitectCommented:
Use the below version:
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

0
 
egypt123Author Commented:
Thanks!
0
 
egypt123Author Commented:
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.
0
 
Muhammad Ousama GhazaliSolution Analyst & ArchitectCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now