Dear all,
Please have a look at my problem.
.
SELECT CURR, DT, AMT, RATE,
SUM(AMT) OVER (PARTITION BY CURR ORDER BY DT) SUMOVER,
sum( amt * rate) over (PARTITION BY CURR ORDER BY DT) / SUM(AMT) OVER (PARTITION BY CURR ORDER BY DT) avgrt
FROM
(
select 'CHF' CURR, ADD_MONTHS(TO_DATE('01-DEC-07'), LEVEL -1) DT, 100 * LEVEL AMT, 1 + ( 5* LEVEL/100) RATE
FROM DUAL CONNECT BY LEVEL < 10
)
.
SQL> /
.
CUR DT AMT RATE SUMOVER AVGRT
--- --------- ---------- ---------- ---------- ----------
CHF 01-DEC-07 100 1.05 100 1.05
CHF 01-JAN-08 200 1.1 300 1.08333333
CHF 01-FEB-08 300 1.15 600 1.11666667
CHF 01-MAR-08 400 1.2 1000 1.15
CHF 01-APR-08 500 1.25 1500 1.18333333
CHF 01-MAY-08 600 1.3 2100 1.21666667
CHF 01-JUN-08 700 1.35 2800 1.25
CHF 01-JUL-08 800 1.4 3600 1.28333333
CHF 01-AUG-08 900 1.45 4500 1.31666667
.
Table Revaluation
.
select 'CHF' CURR1, '31-DEC-07' DT , 1.08 RATE FROM DUAL UNION ALL
select 'CHF' CURR1, '31-MAR-08' DT , 1.22 RATE FROM DUAL UNION ALL
select 'CHF' CURR1, '30-JUN-08' DT , 1.38 RATE FROM DUAL
.
CUR DT RATE
--- --------- ----------
CHF 31-DEC-07 1.08
CHF 31-MAR-08 1.22
CHF 30-JUN-08 1.38
.
.
Problem is with the calculation of average rate.
I want to consider the data in the revaluation table to be used in the calculation of
average rate.
.
So average rate for Jan-08 will be
(100 * 1.08(dec revaluation rate) + 200 * 1.1 ) / (300) = 1.093333333
.
for Feb-08
(100 * 1.08(dec revaluation rate) + 200 * 1.1 + 300 * 1.15) / (600) = 1.121666667
.
for mar-08
(100 * 1.08(dec revaluation rate) + 200 * 1.1 + 300 * 1.15 + 400 * 1.2) / (1000) = 1.153
.
for Apr-08
(1000 * 1.22(Apr revaluation rate) + 500 * 1.25) /1500 = 1.23
.
for May-08
(1000 * 1.22(Apr revaluation rate) + 500 * 1.25 + 600 * 1.30 ) /2100 = 1.25
and so on..
.
Kindly advice