Solved

SQL previous month + current month issue...

Posted on 2010-11-11
3
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

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…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 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