Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL 2005 Date Query Question

Posted on 2009-05-15
8
Medium Priority
?
472 Views
Last Modified: 2013-12-07
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
Comment
Question by:egypt123
  • 3
  • 3
  • 2
8 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 24396455
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
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24396721
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
 

Author Comment

by:egypt123
ID: 24396853
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:tigin44
ID: 24396884
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
 
LVL 11

Accepted Solution

by:
Muhammad Ousama Ghazali earned 2000 total points
ID: 24396946
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
 

Author Closing Comment

by:egypt123
ID: 31581989
Thanks!
0
 

Author Comment

by:egypt123
ID: 24397173
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
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24397539
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Via a live example, show how to take different types of Oracle backups using RMAN.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question