This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 15, 2013 6:58 AM by sliderrules RSS

Display Missing Months

sliderrules Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks that works cleverly enough
  • 10. Re: Display Missing Months
    chris227 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points