Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2012
    Posts
    16

    Exclamation Unanswered: Moving Average Help

    I am looking to reform my code and take the average of the previous 3 months. Currently I am taking the average of the current month plus two previous months, but I would like to not include the current months production at all but take the last 3 months production.

    Ex:
    Year + Month Production
    201109 1439
    201110 1641
    201111 1786
    201112 1326
    201201 1534

    I want to show the average for 201201 to be (1326 + 1786 + 1641) / 3.

    My current code is:
    SELECT
    z.OperatorID,
    z.RRCID,
    z.YearReported,
    z.MonthReported,
    AVG(1e0 * d.GasProduction) AS 'MovingAvg'
    INTO LowProducingWellsv1
    FROM #temp as z
    JOIN #temp as d on (d.OperatorID = z.OperatorID
    AND d.RRCID = z.RRCID
    AND DATEADD(m, 12 * (z.YearReported - 1950) + z.MonthReported, '1949-12-01') BETWEEN DATEADD(m, 12 * (z.YearReported - 1950) + z.MonthReported, '1949-10-01')
    AND DATEADD(m, 12 * (z.YearReported - 1950) + z.MonthReported, '1949-12-01'))
    GROUP BY z.OperatorID, z.RRCID, z.YearReported, z.MonthReported
    HAVING 3 = COUNT(*)
    AND AVG(d.GasProduction) <= 2700
    ORDER BY RRCID, YearReported, MonthReported

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    So what is the issue?

  3. #3
    Join Date
    Jan 2012
    Posts
    16
    It's not averaging the correct previous three months. My records begin at 200801 and must run through the months 201206.

  4. #4
    Join Date
    Jan 2012
    Posts
    16
    I'm just looking to adjust my Join statement to take the previous three months average production. Currently it's taking the current Year+Month plus two previous months average production.

    Any help would be greatly appreciated and is crucial.


    SELECT
    z.OperatorID,
    z.RRCID,
    z.YearReported,
    z.MonthReported,
    AVG(1e0 * d.GasProduction) AS 'MovingAvg'
    INTO LowProducingWellsv1
    FROM #temp as z
    JOIN #temp as d on (d.OperatorID = z.OperatorID
    AND d.RRCID = z.RRCID
    AND DATEADD(m, 12 * (d.YearReported - 1950) + d.MonthReported, '1949-12-01') BETWEEN DATEADD(m, 12 * (z.YearReported - 1950) + z.MonthReported, '1949-10-01')
    AND DATEADD(m, 12 * (z.YearReported - 1950) + z.MonthReported, '1949-12-01'))
    GROUP BY z.OperatorID, z.RRCID, z.YearReported, z.MonthReported
    HAVING 3 = COUNT(*)
    AND AVG(d.GasProduction) <= 2700
    ORDER BY RRCID, YearReported, MonthReported

  5. #5
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Any help would be greatly appreciated and is crucial.
    If this is true, a public forum may not be the best place to look for help - most forums do not "do" urgent.

    Suggest you ask your dba or other database support.
    Last edited by papadi; 09-27-12 at 18:11.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It looks a tad more complicated than it may need to be. why not something like:
    Code:
    create table test1
    (monthno int,
     prod int)
    go
    insert into test1 values
    	(201109, 1439),
    	(201110, 1641),
    	(201111, 1786),
    	(201112, 1326),
    	(201201, 1534)
    go
    declare @thismonth int
    
    set @thismonth = 201201;
    with lastthree
    as
    (select top 3 monthno, prod
     from test1
     where monthno < @thismonth
     order by monthno desc)
    
    select avg(prod)
    from lastthree
    
    drop table test1
    Note: the test1 table is just to hold the test data, so others can try out their solutions, as well.
    Last edited by MCrowley; 09-27-12 at 16:41. Reason: Fixed CTE name

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by papadi View Post
    If this is true, a public forum may not be the best place to look for help - most forums do not "do" urgent".

    Suggest you ask your dba or other database support.
    His question is perfectly appropriate for dbforums. If you don't want to provide an answer or don't have the time, then don't. But don't lead him to believe that some other forum regular won't help him out.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    But don't lead him to believe that some other forum regular won't help him out.
    Did not mean to imply that we wouldn't help. . .

    DID mean to say that if something is critical to the person or the business, a public forum is probably Not the best place to go to find immediate help. It is an ugly "sell" to tell one's manager or client that the answer has to wait for someone to post a reply on the forum . . .

    Just a caution, nothing more.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Many questions on this forum are answered within an hour.
    His question was perfectly appropriate. Your response was not.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Many questions on this forum are answered within an hour.
    Very many. And many continue for days. From what has been posted, i do not know if this has been resolved.

    His question was perfectly appropriate. Your response was not.
    Uncle! This would be an example of the Golden Rule - Them with the Gold make the rules. And as the moderators have the gold - i yield<g>. If "you" believe i should not caution people about solely depending on a forum, i Will refrain.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I guessed I must have missed the post where he said he was solely depending on the forum.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    I may have also - in this topic. Seems like i've interleaved this and another where the poster was concerned if he didn't a solution from us he was going to be in big trouple with his manager. And the way things have gone recently, i may have that one bent around the axle as well . . .

    Mea Culpa,

    d

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •