I need to be able to look at data for a rolling 4 Quarter Period

Question

I need to be able to look at data for a rolling 4 Quarter Period

skailey

Mr or Mrs. 500

Points: 584
More actions
January 18, 2010 at 9:07 am

#140717

Hello,
I am trying to determine a way to run my report based on dates within the last 4 quarters from the current date. For example... if I am running the report anytime between January & then end of March (Quarter 1), I need the report to return data from Quarters 1-4 of last year. If I am running the report anytime from April 1 - June 30, I need the report to pull Quarters 2,3,4 from last year and Q1 from this year. I need the oldest Quarter to fall off the report each time a new Quarter has started. Any ideas?
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

SELECT List, Of, Columns FROM dbo.Whatever WHERE PostingDate Between @StartDate AND @EndDate

Then you can either add the grouping in the reporting engine (they all do this). Or you can group by DATEPART(q, PostingDate)

skailey Mr or Mrs. 500 Points: 584 More actions · Answer 2

Thanks Ninja.

What I am doing is creating a view to pull this info. I need the view to only pull the data on the last 4 quarters... So.. my goal is to have the oldest quarter fall off once a new quarter has started. How can I do that with the Datepart function?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Prehaps these will get you started (Copied from blog posting by Lynn Pettis)

select dateadd(qq, datediff(qq, 0, @ThisDate), 0) -- Beginning of this quarter (Calendar)

select dateadd(qq, datediff(qq, 0, @ThisDate) + 1, 0) -- Beginning of next quarter (Calendar)

select dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0) -- Beginning of previous quarter (Calendar)

select dateadd(yy, datediff(yy, 0, @ThisDate), 0) -- Beginning of this year

--Remove time from datetime data type

SELECT dateadd(day,datediff(day, 0,'2009-10-21 14:34:25.543'),0)

Also these articles on SSC:

http://www.sqlservercentral.com/articles/function/67046/

/* Takes in a Fiscal Year and a Date Returns a string representing the year and the period in which
the date falls based on a 4-4-5 accounting method.*/

http://www.sqlservercentral.com/articles/function/68323/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

skailey Mr or Mrs. 500 Points: 584 More actions · Answer 4

Thanks Bit! This line works perfectly from the blog post with a change in a variable.

select dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0)

Change that to -4 and it works exactly how I need it to.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

skailey

If those worked for you visit Lynn Pettis blog on SSC and learn a great deal more on some neat datetime handling T-SQL

https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]