Solved

Calculate quarter-to-date

Posted on 2009-07-10
33
496 Views
Last Modified: 2012-05-07
I have looked around and couldn't find this.
I have a table with month date (mm/dd/yyy) and scores.  I need to get the current quarter-to-date values.  I think I'd have to get the datepart of the current month and then use an iif statement to figure out which (if any additional months) are needed.
Or is a preferred method to create a reference table with all the months and then code them by Quarter-to-Date values?
0
Comment
Question by:billb1057
  • 14
  • 12
  • 7
33 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24826784
You can use this to get to the start of the current quarter:
dateadd(q, datediff(q, 0, getdate()), 0)
select *

from scores

where score_date >= dateadd(q, datediff(q, 0, getdate()), 0)

Open in new window

0
 
LVL 2

Author Comment

by:billb1057
ID: 24826826
That looks great -- but I'm guessing it doesn't work because I'm using Access 2003 (???).  I got an error "undefined function 'getdate' in expression".
Is there another way around that?
Thanks for your quick reply.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24826856
use date() instead
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24826890
And it should be
....
where score_date >= dateadd("q", datediff("q", 0, date()), 0)
 
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24826921
Actually if you are talking about the start of the current quarter, it should be
where score_date >= dateadd("q", datediff("q", 0, date())-1, 0)+1

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24826923
Thanks, Ralmada.  I didn't see it was MS Access before.
0
 
LVL 2

Author Comment

by:billb1057
ID: 24826999
We're getting a lot closer. :-)
This one works for the beginning of the current quarter.  So, if the file has July 2009, that's the beginning of the 3rd quarter and it works great.
But I'll be reporting on the quarter-to-date of the close of the prior month.  So that would be April 2009 for last month's quarter to date.  Is that just removing the "-1"?  (I haven't been able to break apart what the code is doing yet).
Thanks for the help - to you both.
0
 
LVL 2

Author Comment

by:billb1057
ID: 24827029
Sorry -- that's April 2009 as the beginning of last month's quarter to date.  It's also the beginning of the QTD for May and April.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24827083
What my original code was doing was getting the quarter difference of today from base date which gets you first date of quarter.  In MS Access it gets the last day, so Ramalda used the quarter difference - 1 to get last day of quarter previous then add one day.

So you can do -2 to get last day of quarter previous to prior period.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24827101
And now since you are not getting quarter to date which would be start of current quarter to present, you have to bind the top end of dates as well.  
where score_date >= dateadd("q", datediff("q", 0, date())-2, 0)+1

and score_date < dateadd("q", datediff("q", 0, date())-1, 0)+1

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 24827147
And actual this better mimics what I was trying to do using MS SQL Syntax which has a base date of 1900-01-01.
where score_date >= DateAdd("q",DateDiff("q",#1900-01-01#,Date())-1,#1900-01-01#) 

and score_date < DateAdd("q",DateDiff("q",#1900-01-01#,Date()),#1900-01-01#) 

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24827159
@mwvisa1, I love this approach, but for some reason the above is not working in my MSAccess enviroment.
So I would suggest this to get the previous quarter

DateAdd("q",DateDiff("q",0,Date())-2,2)

Open in new window

0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 250 total points
ID: 24827179
So
DateAdd("q",DateDiff("q",0,Date())-2,2) will give you 1/4/2009

DateAdd("q",DateDiff("q",0,Date())-1,2) will give you 1/7/2009

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24827183
Think it is the starting date difference for value 0 in MS Access vs MS SQL.  I tested my post above and it works exactly the same as the one for MS SQL with #1900-01-01# in place of 0.
0
 
LVL 2

Author Comment

by:billb1057
ID: 24827197
This is cool -- both worked great!  I'm learning a lot here.  :-)  
The datediff is looking at the base date (either 0 or the date that mwvisa1 used) and today's date, right?  This returns a number representing the quarter (it's a big number when I do it).  Then, we subtract 2, to go to the prior quarter.  Then we add 2  ... ???
I'm a bit lost in the logic but it's a great solution either way.
Many thanks again to you both! I hope I can award full points to more than one.
 
0
 
LVL 2

Author Comment

by:billb1057
ID: 24827227
oops, I just substituted
DateAdd("q",DateDiff("q",0,Date())-2,2)
for the previous beginning and the other part to end.  So that works to get between April and June.
The other one
DateAdd("q",DateDiff("q",0,Date())-1,2)
Gives July and that would be 4 months of data not the three between April - June
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:billb1057
ID: 24827233
Sorry - that does work because it's "<" 7/1/2009 and it will give everything in between.
Many thanks again.
0
 
LVL 2

Author Closing Comment

by:billb1057
ID: 31602235
Excellent job in working through this issue.  I appreciate all of your effort to get the SQL right.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24827250
It will give you 1/7/2009, but if you are using < instead of <=, then it will search for values up to June 30 only
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24827258
DateAdd("q",DateDiff("q",#1900-01-01#,Date())-1,#1900-01-01#)

In MS SQL 0 == #1900-01-01#.

DateDiff("q", #1900-01-01#, Date()) == gets the number of quarters since #1900-01-01# to today's date.
Using DateAdd, we add this result back to base date but instead of today's date you get the 1st day.  This works different in MS Access because I think date 0 is something like 1989-12-30 which is why starting at day 2 works the same.

It is easier to understand that -1 is one quarter back than -2, so good to understand what is happening.

This would also work:
where score_date >= DateAdd("q",DateDiff("q",2,Date())-1,2) 

and score_date < DateAdd("q",DateDiff("q",2,Date()),2) 

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24827292
Yes, that's right! 0 is 30-12-1899 in Access. Did a quick test like this
select  format(0, "dd/mm/yyyy")
 
0
 
LVL 2

Author Comment

by:billb1057
ID: 24827300
That is interesting.  The datediff is counting all of the quarters from the base date. I wonder if that's going to work each month now because we don't always need to go back one quarter, right?  Sometimes we're in the middle of the quarter.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24827303
LOL, yes 1899 not 1989. :)
0
 
LVL 2

Author Comment

by:billb1057
ID: 24827314
So, at the end of July (first week of August), want to know QTD for July.  That will be just the one month of July.  But it looks like this will take us back one quarter (sorry if I'm confusing this more).
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24827320
Yes you will always be reporting a quarter behind the way you asked for it to go back to april.  If you want the current quarter just go with my original suggestion and just do this with our updated knowledge on use of 2 instead of 0.
where score_date >= dateadd("q", datediff("q", 2, date()), 2)

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24827346
If you meant to get quarter to date from the last full month, then do it this way:
where score_date >= dateadd("q", datediff("q", 2, dateadd("m", -1, date())), 2)

and score_date < dateadd("q", datediff("q", 2, dateadd("m", -1, date()))+1, 2)

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24827359
This month, that will use 2009-04-01 to 2009-07-01.  In August, it will use 2009-07-01 to 2009-10-01, and so on.
0
 
LVL 2

Author Comment

by:billb1057
ID: 24827535
I appreciate your patience -- I notice that I didn't ask the original question clearly.
In August, I will look for the quarter-to-date for July.
July will be one month, QTD.
In September, I look to August QTD
August will be 2 months for QTD (July & August).
In October, I look to September.
September will be 3 months for QTD (July, August, Sept).
November I look to October.
October will be back to one month - like July.  
0
 
LVL 2

Author Comment

by:billb1057
ID: 24827556
Quarter-to-date is like year-to-date but just starting at the beginning of the quarter through the target month.
So, it's either one, two or three months of data in the calculation.
I think we may need to figure out the month value and then tie it to the quarter.
In August, it will use 2009-07-01 to 2009-10-01, and so on.
Ok, that can't work because we won't have any data for Sept and Oct  in the August report.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24827559
This will work for that -- http:#24827346.  You can plug in dates in future instead of date() in the solution to test out.
0
 
LVL 2

Author Comment

by:billb1057
ID: 24827567
I think we're very close but not quite.
Maybe we change the code to figure out the beginning of the quarter of the prior month.
So, reporting in September -- we look at the prior month (August).  That is in the 3rd quarter and we measure between the beginning of the 3rd quarter and August.
0
 
LVL 2

Author Comment

by:billb1057
ID: 24827579
Yes -- that's right.  It gets the month and then the quarter.  Thanks again.  It does work.
0
 
LVL 2

Author Comment

by:billb1057
ID: 24827612
That works great.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now