Rolling Quarterly data - current 4 quarters

I posted this question about getting a rolling quarterly data query where only the current 4 quarters are showing and my numbers are still off:
http://www.experts-exchange.com/Databases/MS_Access/Q_21408454.html

The query has this:
SELECT Format([date_keyed],"yyyy" & "-" & "q") AS Quarter
and this in hopes to show the rolling current 4 quarters
 (((date_keyed)>=((Date()-Month(Date())+1)-300))

so as of today only these should show: 2004 3, 2004 4, 2005 1, 2005 2 but it's cutting some numbers due to this
 (((date_keyed)>=((Date()-Month(Date())+1)-300))---if I increase it to 365 I will show 5 quarters...and I only want the current 4 but it should be rolling. Is that possible with quarterly data?
chrisamuelAsked:
Who is Participating?
 
jkorzCommented:
a year ago could be in the middle of a quarter though

and I screwed up...

it should be:

where (DateDiff("q", [date-keyed], date()) <=3)

because the current quarter will return 0
0
 
jkorzCommented:
you should never just subtract from a date, use the DateAdd function here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctDateAdd.asp

unless this is for sql server in which case you would want this one (select dateadd function on left):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp
0
 
NestorioCommented:
Try,

date_keyed >= dateadd("yyyy",-1,date())
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GRayLCommented:
Select Format([date-keyed],"yyyy" & "-" & "q") as Quarter where
[date-keyed] between date()-365 and date()
0
 
GRayLCommented:
Sorry, to be proper:

Select Format([date-keyed],"yyyy" & "-" & "q") as Quarter where
[date-keyed] between dateadd("yyyy",-1,date()) and date()
0
 
chrisamuelAuthor Commented:
but that doesn't give me only the current 4 quarters---is that even possible?
0
 
GRayLCommented:
The dateadd() function will even take care of Leap Years.
0
 
chrisamuelAuthor Commented:
I think I'm saying it wrong - what I mean is the current rolling 4 quarters so right now I should see some of 2004 data too so I really can't limit it to just this year
0
 
jkorzCommented:
where (DateDiff("q", [date-keyed], date()) <=4)
0
 
NestorioCommented:
Then change the criteria to this:

date_keyed >= dateadd("m",-9,date())
0
 
GRayLCommented:
My select query will calculate the quarter for every record where the date is between now and a year ago.
0
 
GRayLCommented:
You would of course include other fields in the Query. I also noticed [date-keyed] should be [date_keyed].  You should really avoid using special characters and spaces in field and table names.  You must then wrap them in square brackets - not easy for us poor typists.  DateKeyed is just as informative as date_keyed and a lot easier to create.
0
 
chrisamuelAuthor Commented:
still getting too many quarters - only 4 rolling quarters, so since it's may, I would get 2 quarters from the end of last year and the 2 beginning quarters of this year including the one we are in right now
0
 
jkorzCommented:
I plugged this into my app and it worked like a charm

SELECT Format([date_keyed],"yyyy" & "-" & "q") AS Quarter
WHERE (DateDiff("q", [date_keyed], date()) <=3)
0
 
chrisamuelAuthor Commented:
i think year needs to be added to this so i don't get 2003 but not sure where to put it
date_keyed >= dateadd("m",-9,date())
0
 
chrisamuelAuthor Commented:
SELECT Format([date_keyed],"yyyy" & "-" & "q") AS Quarter
WHERE (DateDiff("q", [date_keyed], date()) <=3)

gives me
Quarter
2003-4
2004-1
2004-2
2004-3
2004-4
2005-1
2005-2
0
 
jkorzCommented:
Chrisamuel:

the big problem with this line:

date_keyed >= dateadd("m",-9,date())

is that it will not start on july 1 of 2004 but rather july 13 2004

0
 
jkorzCommented:
That is really odd... I get

2004-3
2004-4
2005-1
2005-2
0
 
GRayLCommented:
I see it now. Try:

Select Format([date-keyed],"yyyy" & "-" & "q") as Quarter where
[date-keyed] between dateserial(year(date())-1,(format(date(),"q"))*3,1) and date()
0
 
chrisamuelAuthor Commented:
had an Or statement that was throwing it off - sorry
0
 
GRayLCommented:
Sorry, that should be:

Select Format([date_keyed],"yyyy" & "-" & "q") as Quarter where
[date_keyed] between dateserial(year(date())-1,(format(date(),"q"))*3,1) and date()

Darned underscores!!
0
 
jkorzCommented:
whew... I am not crazy... lol

glad I could help
0
 
GRayLCommented:
I assume there are no entries past date().
0
 
GRayLCommented:
jkorz:  Nice post!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.