-
YTD Query
Our fiscal year starts April 1. So each YTD query has a start date of April 1. Problem is if a query is run in Jan then DateSerial(Year(Date()),4,1) does not work. How can I adjust the query below to make sure if then query is run in Jan it gets 4/1 of the previous year and if the query is run in Nov it gets 4/1 of that year? Please help. Will I need to use a IFF statement in the query. Please help. Thanks.
If today is 11/6/2010 then the query below sums cart fee values between 4/1/2010 and 11/6/2010, as well as 4/1/2009 and 11/6/2009. I do this because the query controls a graph which compares this year to last year.
SELECT DISTINCT
(SELECT Sum([GOLF SHOP OPERATIONS].CartFees)
FROM [GOLF SHOP OPERATIONS]
WHERE ((([GOLF SHOP OPERATIONS].TheDate) Between DateSerial(Year(Date()),4,1) And Date()))) AS Expr1,
(SELECT Sum([GOLF SHOP OPERATIONS].CartFees)
FROM [GOLF SHOP OPERATIONS]
WHERE ((([GOLF SHOP OPERATIONS].TheDate) Between DateSerial(Year(Date()) -1,4,1) And Date()-365))) AS Expr2
FROM [GOLF SHOP OPERATIONS];
To simplify, if I run a query using
WHERE ((([GOLF SHOP OPERATIONS].TheDate) Between DateSerial(Year(Date()),4,1) And Date()))
in November then the result will be correct because it sums all values between 4/1 and the present day.
However I think that when I run that same query in Jan the DateSerial(Year(Date()),4,1) will get 4/1/ of that year, when I need 4/1 of the previous year. So it will try to sum data between 4/1 and 1/5 of the same year, which won't work. Thanks again.
Last edited by jasonman; 11-06-2010 at 08:44 AM.
Reason: Clarify
-
Try
Year(Date()) - IIf(Month(Date()) < 4, 1, 0)
-
Hey thank you!!!
Does the query below look correct?
It looks as if its working.
SELECT [GOLF SHOP OPERATIONS].TheDate
FROM [GOLF SHOP OPERATIONS]
WHERE [GOLF SHOP OPERATIONS].TheDate Between DateSerial(Year(Date()),4,1) And Year(Date()) - IIf(Month(Date()) < 4, 1, 0)
BUT HOW.... I need to understand. Please tell me the language behind the query above.
-
Also, the date range being returned includes 4/1/2010. Can I adjust the query just so that only 4/1/2009 - 3/31/2010 for example are returned. Thanks Again.
-
I get a free round, right?
That doesn't look like I would have expected. Try:
WHERE [GOLF SHOP OPERATIONS].TheDate Between DateSerial(Year(Date()) - IIf(Month(Date()) < 4, 1, 0),4,1) And Date()
Basically what it's doing is, within the DateSerial function for the Year argument, it's saying "if the current month is less than 4, subtract 1 from the current year". You can do something similar with the second date if you want it to run to 3/31 instead of the current date.
-
Thanks. Looks like I wanted to exactly! If your ever near the Queen City, u can have a round on me.
-
Happy to help. I'm not familiar with the Queen City, so I guess I've never been near it.
-
Please understand my pain, the Project hangs on these final solutions....now whats happening is as mentioned above the second set of data needs to return 4/1/2008 to Date()-365 if today's date is In jan of 2010. I hope I'm explaining that right. The query returns two YTD sums, one for that current year and one for the previous year. But since our fiscal starts 4/1 then if the query is run in Jan the previous year now actually started two years ago.
My query before is written like this,
HAVING ((([GOLF SHOP OPERATIONS].TheDate) Between DateSerial(Year(Date()) -1,4,1) And Date()-365))) AS SoftGoodsTwo
So if today is Nov 8, 2010 it returns YTD for sums for 4/1/2009-11/8/2009, Perfect! But it won't work next Jan. Run in Jan of 2010 it should return 4/1/2008 to Jan 2009. Please help one more time. Thanks again.
-
Am I missing something? The same technique but subtracting 2 and 1 instead of 1 and 0 would work, would it not?
-
Thanks for walking me through that... So my final solution looks like
SELECT DISTINCT
(SELECT Sum([GOLF SHOP OPERATIONS].CartFees)
FROM [GOLF SHOP OPERATIONS]
WHERE [GOLF SHOP OPERATIONS].TheDate Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1) And Date()) AS Expr1,
(SELECT Sum([GOLF SHOP OPERATIONS].CartFees)
FROM [GOLF SHOP OPERATIONS]
WHERE [GOLF SHOP OPERATIONS].TheDate Between DateSerial(Year(Date()) - IIf(Month(Date()) < 4, 2, 1),4,1) And Date()-365) As Expr2
FROM [GOLF SHOP OPERATIONS];
Thanks again.
-
Happy to help, and hit 'em long and straight!
By the way, you might want to use DateAdd() to subtract a year rather than use Date()-365. It would be a subtle problem, but around leap years you can end up a day off. Not sure about what precision you want there. In other words:
?dateadd("yyyy", -1,#3/1/2012#)
03/01/2011
?#3/1/2012# - 365
03/02/2011
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules