Group By with Dynamic Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By with Dynamic Date

Author

Topic

afifimk
Starting Member

22 Posts

Posted - 2013-08-06 : 20:24:13

I want to modify the following SQL Query to replace the hard-coded date with 5 years back from the current month (The Current Month minus 5 years). I appreciate your help in advance.:


SELECT FirstName, LastName, LatestHireDate, COUNT(*) as "Latest Hire Date"
FROM dbo.EMPLOYEE
WHERE LatestHireDate > '2013-08-01' and ActiveResourceFlag = 'TRUE'
GROUP BY LatestHireDate, FirstName, LastName;

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-06 : 20:54:48

[CODE]
-- CAST(dateadd(MONTH,datediff(MONTH,0,getdate())-60,0) AS DATE) returns '2008-08-01'
SELECT FirstName, LastName, LatestHireDate, COUNT(*) as "Latest Hire Date"
FROM dbo.EMPLOYEE
WHERE LatestHireDate > CAST(dateadd(MONTH,datediff(MONTH,0,getdate())-60,0) AS DATE)
and ActiveResourceFlag = 'TRUE'
GROUP BY LatestHireDate, FirstName, LastName;

[/CODE]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-07 : 02:26:32

SELECT DATEADD(DD, -DAY(GETDATE())+1, DATEADD( YY, -5, GETDATE())) --- 5 years back from current month
GO
-- Alternate is as follows:
WHERE LatestHiteDate > CAST( DATEADD(DD, -DAY(GETDATE())+1, DATEADD( YY, -5, GETDATE())) AS DATE)

--
Chandu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-07 : 03:23:41

[code]SELECT FirstName,
LastName,
LatestHireDate,
COUNT(*) AS [Latest Hire Date]
FROM dbo.Employee
WHERE LatestHireDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19050101', GETDATE()), '19000101')
AND ActiveResourceFlag = 'True'
GROUP BY FirstName,
LastName,
LatestHireDate;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

afifimk
Starting Member

22 Posts

Posted - 2013-08-07 : 08:01:45

Thank you all for your prompt response. The first two solutions are bringing back the same number of records and the third is bringing back two additional records and I will have to do some analysis to figure out why. But all three solutions are bringing far more records than I expected and that's my bad because my question was not very clear. What I need back are the records for the employees who started 5 years ago this month (August). This is intended to be used to post the 5th anniversary for the employees. Hope this makes sense.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-07 : 08:26:36

Here you are grouping by LatestHireDate that's why you got many records than expected.
-- Try this
SELECT FirstName, LastName, COUNT(*) as "Latest Hire Date"
FROM dbo.EMPLOYEE
WHERE (LatestHireDate >= CAST( DATEADD(DD, -DAY(GETDATE())+1, DATEADD( YY, -5, GETDATE())) AS DATE))
and ActiveResourceFlag = 'TRUE'
GROUP BY FirstName, LastName;

--
Chandu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-07 : 08:33:21

[code]SELECT FirstName,
LastName,
LatestHireDate
FROM dbo.Employee
WHERE DATEDIFF(MONTH, LatestHireDate, GETDATE()) = 60
AND ActiveResourceFlag = 'True';[/code]This will work but performance will suffer due to the calculation over LatestHireDate column.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

afifimk
Starting Member

22 Posts

Posted - 2013-08-07 : 08:49:28

quote:
Originally posted by SwePeso

SELECT FirstName,
LastName,
LatestHireDate
FROM dbo.Employee
WHERE DATEDIFF(MONTH, LatestHireDate, GETDATE()) = 60
AND ActiveResourceFlag = 'True';
This will work but performance will suffer due to the calculation over LatestHireDate column.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Awesome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources