YTD Query Issue - 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

SQL Server 2005 Forums

Transact-SQL (2005)

YTD Query Issue

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-02-10 : 14:25:32

I have the following query which grabs year to date volumes from a test table based on a date. The query works for months that contain 30 days. How can I handle months that contain 31 days, and days less than 30 days?

SELECT SUM(volume) AS YTD_Test_Volume
FROM dbo.myTable
WHERE (YEAR(Begin_Date) = YEAR(GETDATE() - 30) - 1) AND (a_column LIKE 'test')

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-02-10 : 14:41:51

SELECT SUM(volume) AS YTD_Test_Volume
FROM dbo.myTable
WHERE Begin_Date BETWEEN DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(MONTH,-1, GETDATE())), 0)
AND DATEADD(ms, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(MONTH,-1, GETDATE()))+1, 0))
AND (a_column LIKE 'test')

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-02-10 : 15:44:39

Thanks, this works fine for an entire year...

But what if the month is January, and we want to get last years records for the whole year?

if january
get last years records for the entire year
other wise
get this years records ytd

Thank you!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-10 : 16:22:52

do you mean this?

WHERE (YEAR(Begin_Date) = YEAR(dateadd(month,-1,GETDATE())) AND (a_column LIKE 'test')

No, you're never too old to Yak'n'Roll if you're too young to die.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-02-10 : 17:00:57

quote:
But what if the month is January, and we want to get last years records for the whole year?

I tested the code with January dates and worked as you requested. You can test it with the following:

SELECT SUM(volume) AS YTD_Test_Volume
FROM dbo.myTable
WHERE Begin_Date BETWEEN DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(MONTH,-1, GETDATE()-30)), 0)
AND DATEADD(ms, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(MONTH,-1, GETDATE()-30))+1, 0))
AND (a_column LIKE 'test')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources