I want to write query in weighted average.
test data
Create Table Rl(
stat varchar2(1),
ddate date,
price number,
units number,
amount number)
insert into rl (stat,ddate,price,units,amount)
values('I',to_date('30.06.2011','dd/mm/yyyy'),10.3594,9903602.1439,102595376)
insert into rl (stat,ddate,price,units,amount)
values('I',to_date('02.07.2011','dd/mm/yyyy'),10.1743,134363.8114,0)
insert into rl (stat,ddate,price,units,amount)
values('I',to_date('02.07.2011','dd/mm/yyyy'),10.2943,136412.1052,0)
insert into rl (stat,ddate,price,units,amount)
values('R',to_date('20.07.2011','dd/mm/yyyy'),10.1461,4928001.8924,0)
insert into rl (stat,ddate,price,units,amount)
values('I',to_date('02.08.2011','dd/mm/yyyy'),10.2943,1778.4053,0)
insert into rl (stat,ddate,price,units,amount)
values('I',to_date('02.08.2011','dd/mm/yyyy'),10.1743,45422.5535,0)
insert into rl (stat,ddate,price,units,amount)
values('I',to_date('05.09.2011','dd/mm/yyyy'),10.2943,1791.0158,0)
insert into rl (stat,ddate,price,units,amount)
values('I',to_date('05.09.2011','dd/mm/yyyy'),10.1743,45744.6419,0)
insert into rl (stat,ddate,price,units,amount)
values('R',to_date('19.09.2011','dd/mm/yyyy'),10.1547,4923828.3750,0)
insert into rl (stat,ddate,price,units,amount)
values('I',to_date('01.10.2011','dd/mm/yyyy'),10.1743,3791.4070,0)
result of table
select stat,ddate,price,units,amount from rl
STAT DDATE PRICE UNITS AMOUNT
---- ------------------------ ---------- ---------- ----------
I 30/06/2011 10.3594 9903602.14 102595376
I 02/07/2011 10.1743 134363.811 0
I 02/07/2011 10.1743 134363.811 0
I 02/07/2011 10.2943 136412.105 0
R 20/07/2011 10.1461 4928001.89 0
I 02/08/2011 10.2943 1778.4053 0
I 02/08/2011 10.1743 45422.5535 0
I 05/09/2011 10.2943 1791.0158 0
I 05/09/2011 10.1743 45744.6419 0
R 19/09/2011 10.1547 4923828.37 0
I 01/10/2011 10.1743 3791.407 0
11 rows selected
I want result like
Units Balance and amount balance are running balance.
if stat = 'I'
Amount balance / unit balance = AVg_price
if stat = 'R'
copy above avg_price * units = Amount
example
stat = I
Row No 1 . 102595376 / 9903602.1439 = 10.3594 avg_price
Row No 2 . 102595376 / 10037965 = 10.2207 avg_price
Row No 3 . 102595376 / 10174378 = 10.0837 avg_price
If Stat = R
copy Avg_price 10.0837
Row No 4 . avg_price 10.0837 * units 4928001.8924 = amount 49692496
Row No 5 . 52902883 / 5248154 = 10.803 avg_price
.
.
Row No 9 .
Stat = R
copy above avg_price
9.9048 avg_price * untis 4923828 = amount 48769746
last row . same as row no 1.2.3
If stat = I
4133136.55 / 421003 = avgprice 9.8173
please see this link for result.
http://www.mypicx.com/10202011/avg/
kindly help