Solved

SQL previous month + current month issue...

Posted on 2010-11-11
3
434 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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