1 2 Previous Next 15 Replies Latest reply: Feb 15, 2013 8:58 AM by sliderrules RSS

    Display Missing Months

    sliderrules
      Hi,

      Please refer to Re: Calculate average over last 12 months

      The analytical function to calculate average over last 12 months does no take into account missing months that contain no data. This causes a discrepancy in the average calculation as I would still like to take the missing months into account even if the value is 0.

      Can anyone suggest the best work around?
        • 1. Re: Display Missing Months
          chris227
          I dont understand. To which solution do you refer?
          As far as i can see in my solution there is always divided by 12, which seems to be what you want. I didnt use the AVG-function exactly on this reason.
          You may also notice my last post in this thread mentioned.
          • 2. Re: Display Missing Months
            Frank Kulash
            Hi,

            If you want to include months that don't appear in your actual results, then outer join your actual results to something that does include all the months you need. For example:
            WITH     months_to_show     AS
            (
                 SELECT     TRUNC ( ADD_MONTHS ( SYSDATE
                                         , 1 - LEVEL
                                   )
                            , 'MONTH'
                            )          AS month_start
                 FROM    dual
                 CONNECT BY     LEVEL <= 12
            )
            ...
            This generates 12 rows, one for the current month and each of the 11 preceding months.


             

            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
            Explain, using specific examples, how you get those results from that data.
            Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}

            If some of this is already on another thread, just copy it to this thread.
            • 3. Re: Display Missing Months
              sliderrules
              Hi,

              As previous post I am trying to calculate the average value per customer and month starting and including the month and 12 months back
              Please see test script below on 2 customers:

              create table test(
              cust_num varchar2(5),
              month date,
              agreed_rate number,
              process_rate number);

              insert into test values('A001', '01-JAN-11', 87, 65);
              insert into test values('A001', '01-FEB-11', 56, 62);
              insert into test values('A001', '01-MAR-11', 76, 88);
              insert into test values('A001', '01-APR-11', 45, 69);
              insert into test values('A001', '01-MAY-11', 99, 65);
              insert into test values('A001', '01-JUN-11', 98, 43);
              insert into test values('A001', '01-JUL-11', 87, 70);
              insert into test values('A001', '01-AUG-11', 43, 99);
              insert into test values('A001', '01-SEP-11', 62, 32);
              insert into test values('A001', '01-NOV-11', 34, 65);
              insert into test values('A001', '01-DEC-11', 87, 87);
              insert into test values('A001', '01-JAN-12', 33, 65);
              insert into test values('A001', '01-FEB-12', 56, 23);
              insert into test values('A001', '01-MAR-12', 76, 46);
              insert into test values('A001', '01-APR-12', 67, 69);
              insert into test values('A001', '01-MAY-12', 99, 33);
              insert into test values('A001', '01-JUN-12', 89, 21);
              insert into test values('A001', '01-JUL-12', 87, 77);
              insert into test values('A001', '01-AUG-12', 69, 99);
              insert into test values('A001', '01-SEP-12', 89, 32);
              insert into test values('A001', '01-OCT-12', 43, 20);
              insert into test values('A001', '01-NOV-12', 34, 56);
              insert into test values('A001', '01-DEC-12', 77, 78);
              insert into test values('A002', '01-JAN-11', 87, 65);
              insert into test values('A002', '01-FEB-11', 56, 62);
              insert into test values('A002', '01-MAR-11', 76, 88);
              insert into test values('A002', '01-APR-11', 45, 69);
              insert into test values('A002', '01-MAY-11', 99, 65);
              insert into test values('A002', '01-JUN-11', 98, 43);
              insert into test values('A002', '01-JUL-11', 87, 70);
              insert into test values('A002', '01-NOV-11', 34, 65);
              insert into test values('A002', '01-DEC-11', 87, 87);
              insert into test values('A002', '01-JAN-12', 33, 65);
              insert into test values('A002', '01-FEB-12', 56, 23);
              insert into test values('A002', '01-MAR-12', 76, 46);
              insert into test values('A002', '01-APR-12', 67, 69);
              insert into test values('A002', '01-MAY-12', 99, 33);
              insert into test values('A002', '01-JUN-12', 89, 21);
              insert into test values('A002', '01-JUL-12', 87, 77);
              insert into test values('A002', '01-AUG-12', 69, 99);
              insert into test values('A002', '01-SEP-12', 89, 32);
              insert into test values('A002', '01-OCT-12', 43, 20);
              insert into test values('A002', '01-NOV-12', 34, 56);
              insert into test values('A002', '01-DEC-12', 77, 78);

              Please note that month OCT-11 is missing in the data above for cust A001 but I would like the calculation to take into account OCT-11 with values 0, 0
              Please note that month AUG-11, SEP-11,OCT-11 is missing in the data above for cust A002 but I would like the calculation to take into account AUG-11, SEP-11,OCT-11 with values 0, 0, 0

              e.g.: 01-JAN-12 = average(agreed_rate) & average(process_rate) from '01-FEB-11' and '01-JAN-12'.
              01-FEB-12= average(agreed_rate) & average(process_rate) from '01-MAR-11' and '01-FEB-12'
              01-MAR-12= average(agreed_rate) & average(process_rate) from '01-APR-11' and '01-MAR-12' etc


              Cust - A001:
              e.g. JAN-12 average agreed_rate = (33+87+34+0+62+43+87+98+99+45+76+56)/12
              FEB-12 average agreed_rate = (56+33+87+34+0+62+43+87+98+99+45+76)/12

              The results should be displayed by month(as above example) and cust_num.

              Below is the query currently being used to derive the average per customer from current month and previous 12 months:

              WITH     got_month_num     AS
              (
                   SELECT     cust_num, month, agreed_rate
                   ,     MONTHS_BETWEEN ( TRUNC (month,     'MONTH')
                             , TRUNC (SYSDATE, 'MONTH')
                             ) AS month_num
                   FROM test
              --     WHERE     ...     -- If you need any filtering, put it here
              )
              SELECT     cust_num, month, agreed_rate
              ,     AVG (agreed_rate) OVER ( PARTITION BY cust_num
                                  ORDER BY     month_num
                                  RANGE BETWEEN 12 PRECEDING
                                       AND     CURRENT ROW
                                  ) AS avg_agreed_rate
              FROM     got_month_num
              ORDER BY month
              ;

              I am using version 11.2

              Can anyone suggest the best work around?

              Thanks

              Edited by: sliderrules on 08-Feb-2013 00:51
              • 4. Re: Display Missing Months
                chris227
                Sorry i dont get it yet.
                Even it seems you are ignoring me, i allow me to ask again:
                What was wrong with the statement below, taking account of the average over 12 month already in the last thread, in contrary to the solution you are using now?
                SELECT      cust_num, month, agreed_rate
                ,      round (sum (agreed_rate) OVER ( PARTITION BY  cust_num
                                                ORDER BY month
                                   RANGE
                                   numtodsinterval(month - trunc(add_months(month, -11), 'MM'), 'DAY')
                                   preceding
                                   ) / 12,2) AS avg_agreed_rate
                FROM     test
                ORDER BY  cust_num, month
                Because it is always divided by 12, there is no need to worry about missing months.
                • 5. Re: Display Missing Months
                  sliderrules
                  Hi,

                  Apologies for not getting back to your question.

                  In the query stated the average does divide by 12 months but if a month is missing in the data that will be ignored as in last example post. For example cust_num A001 does not display month OCT-11 and that will be ignored in the average calculation. I am looking for a solution on handling these missing months.
                  Unfortunately the data only displays months where there is data but I would like to include all calendar months even if there was no activity.
                  • 6. Re: Display Missing Months
                    chris227
                    Ok, i see, you want to include the complete rows of month in the result, not in the calculation (because it is already included there):
                    with dates (mn, mx, cust_num) as (
                    select
                     min(trunc(month, 'MM')) mn
                    ,max(trunc(month, 'MM')) mx
                    ,cust_num
                    from test
                    group by
                    cust_num
                    union all
                    select
                     add_months(mn, 1)
                    ,mx
                    ,cust_num
                    from dates
                    where
                    mn < mx
                    )
                    
                    SELECT      dates.cust_num, mn, agreed_rate
                    ,      round (sum (agreed_rate) OVER ( PARTITION BY  test.cust_num
                                                    ORDER BY month
                                       RANGE
                                       numtodsinterval(month - trunc(add_months(month, -11), 'MM'), 'DAY')
                                       preceding
                                       ) / 12,2) AS avg_agreed_rate
                    FROM     test
                    right outer join 
                    dates
                    on
                    (dates.cust_num = test.cust_num
                    and dates.mn       = trunc(test.month,'MM')
                    )
                    ORDER BY  cust_num, mn
                    Edited by: chris227 on 08.02.2013 01:53
                    • 7. Re: Display Missing Months
                      sliderrules
                      Hi,

                      Thanks for getting back. I have tested the data using your query but the results do not provide an average agreed rate for the months that were missing. I have added nvl(round (sum (agreed_rate) OVER ( PARTITION BY test.cust_num
                      ORDER BY month
                      RANGE
                      numtodsinterval(month - trunc(add_months(month, -11), 'MM'), 'DAY')
                      PRECEDING
                      ) / 12,2),0) AS avg_agreed_rate

                      For example cust_num A002 and month OCT-11 the average agreed rate is 0. This should calculate working backwards last 11 months from OCT-11:

                      OCT -11: 87+56+76+45+99+98+87+0+0+0 = 548/12 =45.66 (average agreed rate)

                      In the example above we can only calculate 10 months because 1-JAN-11 is the minimum date.

                      Please let me know if I have missed anything above?

                      Thanks

                      Edited by: sliderrules on 08-Feb-2013 02:40
                      • 8. Re: Display Missing Months
                        chris227
                        sliderrules wrote:
                        OCT -11: 87+56+76+45+99+98+87+0+0+0 = 548/12 =45.66 (average agreed rate)

                        In the example above we can only calculate 10 months because 1-JAN-11 is the minimum date.
                        Is this correct? I think 548 should be divided just by 10.
                        with dates (mn, mx, cust_num) as (
                        select
                        -- add_months(min(trunc(month, 'MM')), - 12) mn
                         min(trunc(month, 'MM')) mn
                        ,max(trunc(month, 'MM')) mx
                        ,cust_num
                        from test
                        group by
                        cust_num
                        union all
                        select
                         add_months(mn, 1)
                        ,mx
                        ,cust_num
                        from dates
                        where
                        mn < mx
                        )
                        
                        SELECT      dates.cust_num, mn, agreed_rate
                        ,      round (sum (agreed_rate) OVER ( PARTITION BY  dates.cust_num
                                                        ORDER BY mn
                                           RANGE
                                           numtodsinterval(nvl(month,mn) - trunc(add_months(nvl(month,mn), -11), 'MM'), 'DAY')
                                           preceding
                                           )
                               /
                                      least (12, count(*) OVER ( PARTITION BY  dates.cust_num
                                                        ORDER BY mn
                                           RANGE
                                           numtodsinterval(nvl(month,mn) - trunc(add_months(nvl(month,mn), -11), 'MM'), 'DAY')
                                           preceding
                                           )
                                      )
                        -- or just 12 instead of the least( ... as in the answers above
                               ,2) AS avg_agreed_rate
                        FROM     test
                        right outer join 
                        dates
                        on
                        (dates.cust_num = test.cust_num
                        and dates.mn       = trunc(test.month,'MM')
                        )
                        ORDER BY  cust_num, mn
                        Edited by: chris227 on 08.02.2013 03:41

                        Edited by: chris227 on 08.02.2013 03:48
                        • 9. Re: Display Missing Months
                          sliderrules
                          Thanks that works cleverly enough
                          • 10. Re: Display Missing Months
                            chris227
                            As you may have noticed the query does make several assumptions. One is that for each cust_num a separate date range is considered.
                            If you will consider the same over all date-range for all customers, you only need to slightly adjust it
                            with dates (mn, mx) as (
                            select
                             min(trunc(month, 'MM')) mn
                            ,max(trunc(month, 'MM')) mx
                            from test
                            union all
                            select
                             add_months(mn, 1)
                            ,mx
                            from dates
                            where
                            mn < mx
                            )
                             
                            SELECT      cust_num, mn, agreed_rate
                            ,      round (sum (agreed_rate) OVER ( PARTITION BY  cust_num
                                                            ORDER BY mn
                                               RANGE
                                               numtodsinterval(nvl(month,mn) - trunc(add_months(nvl(month,mn), -11), 'MM'), 'DAY')
                                               preceding
                                               )
                                   /         12       ,2) AS avg_agreed_rate
                            FROM
                            test
                            partition by (cust_num)
                            right outer join 
                            dates
                            on
                            (dates.mn       = trunc(test.month,'MM')
                            )
                            ORDER BY  cust_num, mn
                            • 11. Re: Display Missing Months
                              sliderrules
                              Hi,

                              Does anyone know if these functions numtodsinterval and least function are supported in oracle database version 11.2.0.1. I have previously been running the query with these functions in version 11.2.0.3 and it has worked fine. I find that I get different results in version 11.2.0.1.

                              Can anyone help?

                              Thanks
                              • 12. Re: Display Missing Months
                                Frank Kulash
                                Hi,
                                sliderrules wrote:
                                Hi,

                                Does anyone know if these functions numtodsinterval and least function are supported in oracle database version 11.2.0.1. I have previously been running the query with these functions in version 11.2.0.3 and it has worked fine. I find that I get different results in version 11.2.0.1.
                                Why do you suspect that the difference is caused by those functions working differently? Other factors, such as different tables in the different databases, seem much more likely.

                                Whenever you have a problem, please post a complete test script (including CREATE TABLE and INSERT statements for sample data) so that the people who want to help you can re-crete the problem and test their ideas. Also post the (correct) results you want from that sample data.
                                See the forum FAQ {message:id=9360002}
                                • 13. Re: Display Missing Months
                                  sliderrules
                                  I can confirm that the tables are exactly the same in both databases. I have already posted the table script and insert statements in this post (table - test), if I try running the last query on this data the results are different in both databases.
                                  • 14. Re: Display Missing Months
                                    chris227
                                    At first i would doubt on the data.
                                    However you may change the join syntax in to oracle style (though we are on advandced 11g version) to exclude any ANSII.style bugs
                                    FROM     test
                                    right outer join 
                                    dates
                                    on
                                    (dates.cust_num = test.cust_num
                                    and dates.mn       = trunc(test.month,'MM')
                                    )
                                    
                                    into
                                    
                                    
                                    FROM
                                     dates
                                    ,test
                                    where
                                    dates.cust_num = test.cust_num(+)
                                    and
                                    dates.mn       = trunc(test.month(+),'MM')
                                    1 2 Previous Next