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.
| Author |
Topic |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2014-03-24 : 10:56:57
|
| Hi there,I need accumulate the months by Quarter measurement. For example we're in March now and I need put these code in Where clause like:Where MyDate > DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) ...here DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) makes Apr of last year included because that is the first month in 2nd Quarter of last year. But in next month we're in Apr and I need make -11 as -12 in above code to include Apr of last year. And same reason if we're in Feb. 2014 then I need change -11 to -10. So how can I calculate this to make the number fit into the code or some other way to get the same effect? I need get all last 4 quarters based on current month (include current Quarter).Thanks in advance. |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-03-24 : 12:23:15
|
| Where is this code to go into a report in SSRS?You will need to add a variable or use an IIF to check the month number and calculate your DATEADD/DATEDIFF according to the result.If you will it you can achieve it!! |
 |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2014-03-24 : 12:41:45
|
quote:
Originally posted by allan8964
Hi there,I need accumulate the months by Quarter measurement. For example we're in March now and I need put these code in Where clause like:Where MyDate > DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) ...here DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) makes Apr of last year included because that is the first month in 2nd Quarter of last year. But in next month we're in Apr and I need make -11 as -12 in above code to include Apr of last year. And same reason if we're in Feb. 2014 then I need change -11 to -10. So how can I calculate this to make the number fit into the code or some other way to get the same effect? I need get all last 4 quarters based on current month (include current Quarter).Thanks in advance.
DATEPART() has a quarter built in so just do that.http://msdn.microsoft.com/en-us/library/ms174420.aspx |
|
|
|
|
|
|
|
|
calculation of Quarter