7 Replies Latest reply on Feb 22, 2013 6:17 PM by chris227

# calculation

hi i am using oracle 10g database,
`````` id             fdt           tdt             amt
100         01-JAN-84       30-JUN-85       10000
100         01-JUL-85       15-NOV-85       15000
``````
SO, i want to find the last 12month average amount. Here less than a month will be count as a full month i.e 15-nov-85 will be taken as a full nov month.

as per the above data--
15-nov-85 to 01-jul-85 = 5 months i.e 5*15000 =75000
30-jun-05 to 01-dec-84=7 months i.e 7*10000=70000

round( (75000+70000)/12)= 12083

final output shouldl be like this
`````` id             fdt           tdt             amt
100         01-DEC-84       15-NOV-85       12083
``````

thanks

Edited by: Hi FRNzzz!! on Feb 11, 2013 9:56 PM

Edited by: Hi FRNzzz!! on Feb 11, 2013 10:04 PM
• ###### 1. Re: calculation

Where is the table and data in a format we can use? (create table and insert statements?)
What is the expected output?

just two rows of example data isn't much for people to understand exactly what you're trying to do.

You should know better by now

{message:id=9360002}
• ###### 2. Re: calculation
This works on 11g:
``````.  ID   FDT       TDT         AMT
1  100  1.7.1983  1.2.1984    10000 --"0"
2  100  1.3.1984  25.1.1985   15000 --"2 * 15000 = 30000"
3  100  1.2.1985  20.7.1985   250   --"6 * 250 = 1500"
4  100  1.8.1985  15.11.1985  1250  --"4 * 1250 = 5000"

--"30000 + 1500 + 5000 = 36500"
--"36500 / 12 = 3042 (rounded)"``````
Code:
``````SELECT DISTINCT id,
Max(fdt_)
over()                 fdt_,
Max(tdt_)
over()                 tdt_,
Round(( SUM(amt_)
over() ) / 12) amt_
FROM   (SELECT id,
over(), -diff + 1), 'MM') fdt_,
Max(tdt)
over()                                     tdt_,
( ( sotsum / somb ) * diff )                 amt_
FROM   (SELECT t4.*,
ROWNUM
rn,
over (
ORDER BY mttdt), 12, somb,
over (
ORDER BY mttdt)
) diff
FROM   (SELECT t3.*,
SUM(tsum)
over (
PARTITION BY mttdt) sotsum,
SUM(mb)
over (
PARTITION BY mttdt) somb
FROM   (SELECT t2.*,
( t2.mb * t2.amt ) tsum,
Decode(ttdt, Min(ttdt)
over(), 0,
1)    mttdt
FROM   (SELECT id,
fdt,
tdt,
amt,
Last_day(tdt)
ttdt,
over(), -12),
'MM')
mmth,
Ceil(Abs(Months_between (Trunc(
fdt),
Last_day(
tdt)))) mb
FROM   (
-----["input table"]-----
select 100 id, to_date('01-07-1983', 'DD-MM-YYYY') fdt, to_date('01-02-1984', 'DD-MM-YYYY') tdt, 10000 amt from dual union all
select 100 id, to_date('01-03-1984', 'DD-MM-YYYY') fdt, to_date('25-01-1985', 'DD-MM-YYYY') tdt, 15000 amt from dual union all
select 100 id, to_date('01-02-1985', 'DD-MM-YYYY') fdt, to_date('20-07-1985', 'DD-MM-YYYY') tdt,   250 amt from dual union all
select 100 id, to_date('01-08-1985', 'DD-MM-YYYY') fdt, to_date('15-11-1985', 'DD-MM-YYYY') tdt,  1250 amt from dual
-----[/"input table"]-----
) t) t2
WHERE  ttdt >= mmth
ORDER  BY ttdt ASC) t3) t4
WHERE  ROWNUM < 3) t5); ``````
Result:
``````.  ID   FDT_      TDT_        AMT_
1  100  1.12.1984 15.11.1985  3042``````
EXPLAIN PLAN:
``````-----------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     2 |    62 |    10  (20)|
|   1 |  HASH UNIQUE              |      |     2 |    62 |    10  (20)|
|   2 |   WINDOW BUFFER           |      |     2 |    62 |    10  (20)|
|   3 |    VIEW                   |      |     2 |    62 |     9  (12)|
|   4 |     WINDOW BUFFER         |      |     2 |   120 |     9  (12)|
|   5 |      VIEW                 |      |     2 |   120 |     9  (12)|
|   6 |       WINDOW BUFFER       |      |     2 |   100 |     9  (12)|
|   7 |        COUNT STOPKEY      |      |       |       |            |
|   8 |         VIEW              |      |     4 |   200 |     9  (12)|
|   9 |          WINDOW SORT      |      |     4 |   200 |     9  (12)|
|  10 |           VIEW            |      |     4 |   200 |     8   (0)|
|  11 |            WINDOW SORT    |      |     4 |   196 |     8   (0)|
|  12 |             VIEW          |      |     4 |   196 |     8   (0)|
|  13 |              WINDOW BUFFER|      |     4 |    96 |     8   (0)|
|  14 |               VIEW        |      |     4 |    96 |     8   (0)|
|  15 |                UNION-ALL  |      |       |       |            |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  16 |                 FAST DUAL |      |     1 |       |     2   (0)|
|  17 |                 FAST DUAL |      |     1 |       |     2   (0)|
|  18 |                 FAST DUAL |      |     1 |       |     2   (0)|
|  19 |                 FAST DUAL |      |     1 |       |     2   (0)|
-----------------------------------------------------------------------``````
• ###### 3. Re: calculation
Something like
``````with data(id,fdt,tdt,amt) as (-- just test data, replace datain the query with the real table name
select 100,to_date('01-JAN-84','DD-MON-RR'),to_date('30-JUN-85','DD-MON-RR'),10000 from dual union all
select 100,to_date('01-JUL-85','DD-MON-RR'),to_date('15-NOV-85','DD-MON-RR'),15000 from dual union all
select 100,to_date('01-JAN-83','DD-MON-RR'),to_date('30-NOV-83','DD-MON-RR'),10000 from dual)

select
id
,max(tdt) tdt
,round(sum(
case when md + mb < 12 then amt * mb
else (12 - md) * amt
end
)/12) amt
from (
select
id,fdt,tdt,amt
,ceil(months_between(max(tdt) over (partition by id order by tdt desc nulls last),tdt)) md
,ceil(months_between(tdt,fdt)) mb
from
data
)
where
md < 12
group by id

ID FDT TDT AMT
100 12/01/1984 11/15/1985 12083 ``````
I admit that i am not quite sure if the 12 in case when md + mb < 12 then amt * mb has to be a 13 or not. May be you will try it out with some test data yourself ;-)

SOme explanation:
md is the distance in months of each tdt to the max(tdt). All rows with a distance higher than 11 can be excluded.
mb is the range of month each row covers.
When distance and range exceed 12 the weight of the amount must be cut down to the remaining number of months to reach 12.
Otherwise each amount is weighted with the range.

Edited by: chris227 on 14.02.2013 02:01
start date corrected

Edited by: chris227 on 14.02.2013 02:03
explanation
• ###### 4. Re: calculation
first of all thanks and query is impresive
as per you query , I CHANGED THE INPUT DATES BUT desired output calculation somewhere not getting
``````  with data(id,fdt,tdt,amt) as (-- just test data, replace datain the query with the real table name
select 100,to_date('01-JUL-85','DD-MON-RR'),to_date('15-JUN-86','DD-MON-RR'),3000 from dual union all
select 100,to_date('16-JUN-86','DD-MON-RR'),to_date('15-NOV-86','DD-MON-RR'),7000 from dual)

select
id
,max(tdt) tdt
,round(sum(
case when md + mb < 12 then amt * mb
else (12 - md) * amt
end
)/12) amt
from (
select
id,fdt,tdt,amt
,ceil(months_between(max(tdt) over (partition by id order by tdt desc nulls last),tdt)) md
,ceil(months_between(tdt,fdt)) mb
from
data
)
where
md < 12
group by id

RESULT GOT
ID     FDT      TDT     AMT
100 12/01/1985 11/15/1986 4667
``````
which should be
``````ID     FDT      TDT      AMT
100 12/01/1985 11/15/1986 4853``````
because
``from 01/06/86 to 15/06/86 and 16/06/86 to 30/06/86 amount calculation should be taken as per the amount and no. of days present respectively``
thanks
• ###### 5. Re: calculation
I just get 4749, how is the calculus to get 4853?
``````with data(id,fdt,tdt,amt) as (-- just test data, replace datain the query with the real table name
select 200,to_date('01-JUL-85','DD-MON-RR'),to_date('15-JUN-86','DD-MON-RR'),3000 from dual union all
select 200,to_date('16-JUN-86','DD-MON-RR'),to_date('15-NOV-86','DD-MON-RR'),7000 from dual)

select
id
,max(tdt) tdt
,round(sum(
case when md  <= 0 then amt * mb
else (mb - md + 1) * amt
end
)/sum(
case when md  <= 0 then mb
else (mb - md + 1)
end)) amt
from (
select
id,fdt,tdt,amt
,trunc(add_months(max(tdt) over (partition by id), - 11),'MM') - fdt + 1 md
,tdt-fdt+1 mb
,months_between(max(tdt) over (partition by id),tdt) mdd
from
data
)
where
mdd <= 12
group by id

ID     FDT     TDT     AMT
200     12/01/1985     11/15/1986     4749``````
close but i need an example of the excat calculation:
``````with data(id,fdt,tdt,amt) as (-- just test data, replace datain the query with the real table name
select 100,to_date('01-JAN-84','DD-MON-RR'),to_date('30-JUN-85','DD-MON-RR'),10000 from dual union all
select 100,to_date('01-JUL-85','DD-MON-RR'),to_date('15-NOV-85','DD-MON-RR'),15000 from dual union all
select 100,to_date('01-JAN-83','DD-MON-RR'),to_date('30-NOV-83','DD-MON-RR'),10000 from dual union all
select 200,to_date('01-JUL-85','DD-MON-RR'),to_date('15-JUN-86','DD-MON-RR'),3000 from dual union all
select 200,to_date('16-JUN-86','DD-MON-RR'),to_date('15-NOV-86','DD-MON-RR'),7000 from dual)

select
id
,max(tdt) tdt
,round(sum(
case when md  <= 0 then amt * mb
else (mb - md + 1) * amt
end
)/sum(
case when md  <= 0 then mb
else (mb - md + 1)
end)) amt
from (
select
id,tdt,amt
,trunc(add_months(max(tdt) over (partition by id), - 11),'MM') - fdt + 1 md
,months_between(trunc(add_months(max(tdt) over (partition by id), - 11),'MM'),tdt) mdd
from
data
)
where
mdd <= 12
group by id

ID     FDT     TDT     AMT
100     12/01/1984     11/15/1985     12096
200     12/01/1985     11/15/1986     4768``````
Edited by: chris227 on 17.02.2013 10:21
• ###### 6. Re: calculation
first of all i am extremely sorry . result will come 4833.

now how it will come as follows
``````15/11/86 to 01/07/86= 7000 *5 = 35000
30/06/86 to 16/06/86= (7000/30)*15=3500 as total days in jun is 30 , so after division multiply the amount with no. of days between 30/06/86 to 16/06/86.
15/06/86 to 01/06/86=(3000/30)*15=1500 same as above line
30/05/86 to 01/12/85=3000*6=18000

so finally 35000+3500+1500+18000=58000/12 comes round(4833.33)=4833``````
hope it will help to understand
• ###### 7. Re: calculation
Sorry for the delay, i was sick and i am still, but not that worse anymore.
May be a variation of my first approach:
``````with data(id,fdt,tdt,amt) as (-- just test data, replace datain the query with the real table name
select 100,to_date('01-JAN-84','DD-MON-RR'),to_date('30-JUN-85','DD-MON-RR'),10000 from dual union all
select 100,to_date('01-JUL-85','DD-MON-RR'),to_date('15-NOV-85','DD-MON-RR'),15000 from dual union all
select 100,to_date('01-JAN-83','DD-MON-RR'),to_date('30-NOV-83','DD-MON-RR'),10000 from dual union all
select 200,to_date('01-JUL-85','DD-MON-RR'),to_date('15-JUN-86','DD-MON-RR'),3000 from dual union all
select 200,to_date('16-JUN-86','DD-MON-RR'),to_date('15-NOV-86','DD-MON-RR'),7000 from dual)

select
id
,max(tdt) tdt
,round(sum(
case when md + mb <= 12 then amt *
decode(md, .5 , round(months_between(trunc(add_months(tdt,1), 'MM'), fdt),1), mb)
else (12 - md) * amt
end
)/12) amt
from (
select
id,fdt,tdt,amt
,round(
months_between(
trunc(add_months(max(tdt) over (partition by id order by tdt desc nulls last),1),'MM')-1
,tdt-1)
,1) md
,round(months_between(tdt,fdt-1),1) mb
from
data
)
where
md < 12
group by id

ID     FDT     TDT     AMT
100     12/01/1984     11/15/1985     12083
200     12/01/1985     11/15/1986     4833``````
I use months_between here. This considers all months as having the same number of days.
So the average for example over the range 01-Feb/15-Feb-16-Feb/28-Feb is calculated the same way as for the other months, although the second half of the month is shorter. But as you go for a monthly average this seems to be the correct way.