1 2 Previous Next 15 Replies Latest reply: Feb 15, 2013 2:58 PM by sliderrules Go to original post RSS
      • 15. Re: Display Missing Months
        sliderrules
        I thought it may be the data but I have run the test scripts in both databases and they appear to be the same.

        I cant seem to figure out the possible causes. I have changed the query as suggested but still no luck:

        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
        DATES
        , test
        where DATES.CUST_NUM = TEST.CUST_NUM(+)
        and dates.mn = trunc(test.month(+),'MM')
        1 2 Previous Next