Solved

SQL 2005 Date Query Question

Posted on 2009-05-15
8
414 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 26

Expert Comment

by:tigin44
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Accepted Solution

by:
Muhammad Ousama Ghazali earned 500 total points
Comment Utility
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
Comment Utility
Thanks!
0
 

Author Comment

by:egypt123
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now