Solved

SQL 2005 Date Query Question

Posted on 2009-05-15
8
426 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 11

Accepted Solution

by:
Muhammad Ousama Ghazali earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 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

24 Experts available now in Live!

Get 1:1 Help Now