12 month moving totals without datetime?

Question

12 month moving totals without datetime?

justindenning

Newbie

Points: 5
More actions
June 24, 2009 at 7:29 am

#73927

I have a view that tracks opportunities and their conversion for each month. I would like a query that shows the 12 month moving totals i.e. January 09 returns the number of opportunities and the number won between January 08 and January 09. The view is comprised of
Year (int)
Month(int)
Opportunities(int)
OppsWon(int)
Thanks for any insight you may have.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The following will give you the total for the last 12 months as requested based on a given variable @yr for the year and @mo for the month.

SELECT SUM(Opportunities),SUM(OppsWon) FROM @t V_iew

WHERE (yer*12+mnth) BETWEEN ((@yr-1)*12+@mo) AND (@yr*12+@mo)

If that's not what you're looking for I'd like you to have a look at the link in my signature on how to post sample data.

Please include expected result set and what you've tried so far.

Note: I'm not sure if it's really a good idea to store year and month in separate columns. Maybe storing it as date format would be easier to handle. Definitely for the task you ask for.

But that's hard to say without knowing what the purpose of the view is...

Also, please note that I modified your column names since I don't think it's a good way to name columns using reserved keywords.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 2

See here how to make a really fast moving average.

N 56°04'39.16"
E 12°55'05.25"