I have a table in which User Update some data which includes Currency and Month.
I have another table containing Exchange Rates month wise.
Now the problem is this exchange rate is populated only at the end of month. So for Sep-14 exchange rate, it will be uploaded say in 29th Sep-14.
Now if someone is entering data at the beginning of month say 1st Sep-14, the exchange is of Sep-14 is not available. So I want to make use of previous month Exchange Rate (latest available Exchange Rate) i.e Aug-14 till such time.
But once Sep-14 exchange is uploaded , I have to use Sep-14 exchange rate.
My Exchange Rate Table is shown below:
| Currency_To | Conversion_Date | Month | Conversion: Type | Conversion_Rate |
| ARS | 29-Aug-14 | Aug--14 | Corporate | 3.73 |
| ALL | 29-Aug-14 | Aug--14 | Corporate | 109.71 |
| ALL | 29-Sep-14 | Sep--14 | Corporate | 109.75 |
| ARS | 29-Sep-14 | Sep--14 | Corporate | 3.55 |
My USER Table:
| Complex Order | Amount | Currency | Month |
| 11 | 123 | ALL | Sep--14 |
| 22 | 345 | ARS | Sep--14 |
| 33 | 678 | ARS | Sep--14 |
| 44 | 890 | ALL | Sep--14 |
I am using the following query. Though I am able to use the latest Exchange Rate, but not able to use for a particular month.
select ,sum(NVL(a.AMOUNT,0)/b.CONVERSION_RATE) AS "COMPLEX ORDER BOOKED"
from USER a, EXCHANGE_RATE b
WHERE b.CURRENCY_TO = a.CURRENCY AND b.CONVERSION_DATE = (SELECT MAX(CONVERSION_DATE) FROM CORPORATE_EXCHANGE_RATE))