SQL previous month + current month issue...

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.
twfw123Asked:
Who is Participating?
 
subhashpuniaConnect With a Mentor Commented:
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
 
sarabhaiConnect With a Mentor Commented:
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
 
rmm2001Connect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.