[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Rolling Quarterly data - current 4 quarters

Posted on 2005-05-13
24
Medium Priority
?
2,487 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:chrisamuel
  • 9
  • 7
  • 6
  • +1
24 Comments
 
LVL 8

Expert Comment

by:jkorz
ID: 13999112
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 13999122
Try,

date_keyed >= dateadd("yyyy",-1,date())
0
 
LVL 44

Expert Comment

by:GRayL
ID: 13999131
Select Format([date-keyed],"yyyy" & "-" & "q") as Quarter where
[date-keyed] between date()-365 and date()
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 44

Expert Comment

by:GRayL
ID: 13999156
Sorry, to be proper:

Select Format([date-keyed],"yyyy" & "-" & "q") as Quarter where
[date-keyed] between dateadd("yyyy",-1,date()) and date()
0
 

Author Comment

by:chrisamuel
ID: 13999163
but that doesn't give me only the current 4 quarters---is that even possible?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 13999165
The dateadd() function will even take care of Leap Years.
0
 

Author Comment

by:chrisamuel
ID: 13999181
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
 
LVL 8

Expert Comment

by:jkorz
ID: 13999189
where (DateDiff("q", [date-keyed], date()) <=4)
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 13999216
Then change the criteria to this:

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

Expert Comment

by:GRayL
ID: 13999224
My select query will calculate the quarter for every record where the date is between now and a year ago.
0
 
LVL 8

Accepted Solution

by:
jkorz earned 2000 total points
ID: 13999253
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
 
LVL 44

Expert Comment

by:GRayL
ID: 13999255
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
 

Author Comment

by:chrisamuel
ID: 13999318
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
 
LVL 8

Expert Comment

by:jkorz
ID: 13999364
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
 

Author Comment

by:chrisamuel
ID: 13999374
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
 

Author Comment

by:chrisamuel
ID: 13999402
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
 
LVL 8

Expert Comment

by:jkorz
ID: 13999411
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
 
LVL 8

Expert Comment

by:jkorz
ID: 13999434
That is really odd... I get

2004-3
2004-4
2005-1
2005-2
0
 
LVL 44

Expert Comment

by:GRayL
ID: 13999435
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
 

Author Comment

by:chrisamuel
ID: 13999436
had an Or statement that was throwing it off - sorry
0
 
LVL 44

Expert Comment

by:GRayL
ID: 13999453
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
 
LVL 8

Expert Comment

by:jkorz
ID: 13999457
whew... I am not crazy... lol

glad I could help
0
 
LVL 44

Expert Comment

by:GRayL
ID: 13999462
I assume there are no entries past date().
0
 
LVL 44

Expert Comment

by:GRayL
ID: 13999477
jkorz:  Nice post!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question