Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

Calculate quarter-to-date

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
billb1057
Asked:
billb1057
  • 14
  • 12
  • 7
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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
 
billb1057Author Commented:
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
 
ralmadaCommented:
use date() instead
0
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!

 
ralmadaCommented:
And it should be
....
where score_date >= dateadd("q", datediff("q", 0, date()), 0)
 
0
 
ralmadaCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
Thanks, Ralmada.  I didn't see it was MS Access before.
0
 
billb1057Author Commented:
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
 
billb1057Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
ralmadaCommented:
@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
 
ralmadaCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
billb1057Author Commented:
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
 
billb1057Author Commented:
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
 
billb1057Author Commented:
Sorry - that does work because it's "<" 7/1/2009 and it will give everything in between.
Many thanks again.
0
 
billb1057Author Commented:
Excellent job in working through this issue.  I appreciate all of your effort to get the SQL right.
0
 
ralmadaCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
ralmadaCommented:
Yes, that's right! 0 is 30-12-1899 in Access. Did a quick test like this
select  format(0, "dd/mm/yyyy")
 
0
 
billb1057Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
LOL, yes 1899 not 1989. :)
0
 
billb1057Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
billb1057Author Commented:
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
 
billb1057Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
This will work for that -- http:#24827346.  You can plug in dates in future instead of date() in the solution to test out.
0
 
billb1057Author Commented:
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
 
billb1057Author Commented:
Yes -- that's right.  It gets the month and then the quarter.  Thanks again.  It does work.
0
 
billb1057Author Commented:
That works great.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 14
  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now