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

Display Missing Months

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
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
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.

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
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
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
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
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
,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
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
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
,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
Thanks that works cleverly enough
• 10. Re: Display Missing Months
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
,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
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
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
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
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