Quarter-to-Date 2

[starbase59]

Hi,
Need help with a Case statement to report figures quarter-to- date.....Could it be similar to the code for month-to-date, such like the code below? For example, if the @paramDate = 01/18/12 then Q1, if @paramDate = 4/18/12, then Q2, and so on.
If so, how? Thank you.

WHERE ((Date BETWEEN
(Case DatePart (mm, @ParamDate)
When 1 then '01/01/12'
When 2 then '02/01/12'
When 3 then '03/01/12'
When 4 then '04/01/12'
When 5 then '05/01/12'
When 6 then '06/01/12'
When 7 then '07/01/12'
When 8 then '08/01/12'
When 9 then '09/01/12'
When 10 then '10/01/12'
When 11 then '11/01/12'
When 12 then '12/01/12'
END)AND @ParamDate))

 

[gmmastros]

I suppose the tricky part here is to get the first day of the quarter, right? Well, there is a relatively simply way to accomplish this:

Select DateAdd(Quarter, DateDiff(Quarter, 0, GetDate()), 0)

Applying this to your query:

Where Date Between DateAdd(Quarter, DateDiff(Quarter, 0, @ParamDate), 0) And @ParamDate

This would also work for your Month-To-Date query (with a slight modification).

Where Date Between DateAdd([!]Month[/!], DateDiff([!]Month[/!], 0, @ParamDate), 0) And @ParamDate

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

you might want a calendar table. Check out...

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Makes quarter queries a sinch.

simi