Solved

SQL previous month + current month issue...

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 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

13 Experts available now in Live!

Get 1:1 Help Now