Solved

SQL previous month + current month issue...

Posted on 2010-11-11
3
413 Views
Last Modified: 2012-05-10
HI All - have a problem that I am struggling to find an easy awnser to that I need some assistance with, details and example below.
I have some SQL to create the following rolling average.
It takes month 1 and adds it to month 2 then /2 i.e. from below table 200911(200) +200912(100)/2 = rolling average result 250, it works great.
id      YYYYMM      amount             rolling average
123      200910      300            
123      200911      200            400
123      200912      100            250
123      201001      100            150
123      201002      200            200
123      201003      300            350

My problem is - if I have a missing months data in the middle of my results set, say if I have no data for 201001, I should get no result for the rolling average of 201002. ?
What I was thinking of doing is creating a # table using the same data with the following case statement
case when a.yyyymm-1= b.yyyymm
                  then (a.amount+isnull(b.amount,a.amount)/2)
       else '0.00' end,
LEFT JOIN tmp b on a.yyyymm=b.yyyymm+1
but it just seemed bit complicated...
Any other suggestions? Hope I have made my self clear here.
0
Comment
Question by:twfw123
3 Comments
 
LVL 6

Accepted Solution

by:
subhashpunia earned 167 total points
ID: 34119340
My approach would be as below:

1. Create a temp table containing 12 records of values 1 to 12 (integer values)
2. From your table data add on computed column that would contain the month number - 1 of your YYYYMM column. For example for value 200910 compute 9, for 200911 compute 10.
3. Now inner join your table with temp table (step #1) on month number.
This way you would skip the row which's previous month is missing.
4. Now left join the rolling avergae value to your table data.
0
 
LVL 9

Assisted Solution

by:sarabhai
sarabhai earned 167 total points
ID: 34119740
please specify ur formula for data is null for previous/current month.

case when a.yyyymm-1= b.yyyymm
                  then (a.amount+isnull(b.amount,0)/2)
       else '0.00' end,
LEFT JOIN tmp b on a.yyyymm=b.yyyymm+1
0
 
LVL 7

Assisted Solution

by:rmm2001
rmm2001 earned 166 total points
ID: 34128809
Go with @subhashpunia. This will eliminate the problem (unless it's intended) that you'll have at the next month after the "0" month. So lets say 200912 is blank and has a "rolling average" = 0. 201001 would be 0 + month/2 as it's currently written
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now