Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Calculate quarter-to-date

Posted on 2009-07-10
Medium Priority
504 Views
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
Question by:billb1057
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 14
• 12
• 7

LVL 60

Expert Comment

ID: 24826784
You can use this to get to the start of the current quarter:
``````select *
from scores
where score_date >= dateadd(q, datediff(q, 0, getdate()), 0)
``````
0

LVL 2

Author Comment

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?
0

LVL 41

Expert Comment

ID: 24826856
0

LVL 41

Expert Comment

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

0

LVL 41

Expert Comment

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
``````
0

LVL 60

Expert Comment

ID: 24826923
Thanks, Ralmada.  I didn't see it was MS Access before.
0

LVL 2

Author Comment

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

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 60

Expert Comment

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 60

Expert Comment

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
``````
0

LVL 60

Accepted Solution

Kevin Cross earned 1000 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#)
``````
0

LVL 41

Expert Comment

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)
``````
0

LVL 41

Assisted Solution

ID: 24827179
So
``````DateAdd("q",DateDiff("q",0,Date())-2,2) will give you 1/4/2009
``````
0

LVL 60

Expert Comment

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

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

ID: 24827227
oops, I just substituted
for the previous beginning and the other part to end.  So that works to get between April and June.
The other one
Gives July and that would be 4 months of data not the three between April - June
0

LVL 2

Author Comment

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

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

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 60

Expert Comment

ID: 24827258

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)
``````
0

LVL 41

Expert Comment

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

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 60

Expert Comment

ID: 24827303
LOL, yes 1899 not 1989. :)
0

LVL 2

Author Comment

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 60

Expert Comment

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)
``````
0

LVL 60

Expert Comment

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)
``````
0

LVL 60

Expert Comment

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

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

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 60

Expert Comment

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

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

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

LVL 2

Author Comment

ID: 24827612
That works great.
0

## Featured Post

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server.Â RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at: Â  htâ€¦
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediafâ€¦
###### Suggested Courses
Course of the Month4 days, 13 hours left to enroll