January 19, 2011 at 8:35 am
I have a series of stored procedures that I use to create a table for our daily reports. One of the factors is a 3 month rolling average of sales and costs. I was using a line of code ship date <= today() - 90. This gave me the last 90 days of shipments. This also changed that average every day because our shipments (like most people's ) are not linear. What he wants to do is get the average for the prior three months. So for example if I am in April, he would like the average of January, February and March. When the month is greater than or equal to 4 the math is simple, but in January February and March it does not work. Plus I now need to include a year in the mix. Here is my thought. I can create a field called ship month and ship year using the stored procedure. What I want to do is create a case statement like:
Case ship month()
When 1
Then ship year = year(today()) - 1 and ship month > 9
When 2
Then (ship year = year(today()) - 1 and ship month > 10) or (ship year = year(today()) and ship month = 1)
When 3
Then (ship year = year(today()) - 1 and ship month = 12) or (ship year = year(today()) and ship month < 3)
Else
Then ship year = year(today()) and (or(ship month = month(today())-1,ship month = month(today())-2,
ship month = month(today())-3)
I think the case statement is close, what I don't know is how to put the results into the where statement in the stored procedure so that I limit the results to the group I have.
Suggestions? I have already told the VP to get a life but thank you if that was your suggestion. lol
January 19, 2011 at 8:46 am
All WHEN clauses need logical statements, though CASE statements deliver typically a value. To solve this, change the script this way:
ship year = CASE ship month()
WHEN ...
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 21, 2011 at 1:40 pm
If your Ship_Date column is of type datetime then you don't need to worry about the extra complication that is introduced by splitting the date into year and month components.
The following WHERE clause will filter rows where the Ship_Date column value is during the previous 3 months (that is the 3 months prior to the current month).
...
WHERE (Ship_Date >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 3, 0))
AND (Ship_Date < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
January 21, 2011 at 2:35 pm
Thank you Andrew. I actually figured it out about 11:30 pm last night while watching Jersey Shores. Watching that makes me feel so smart.:-D
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply