I have two tables, Table1 with amount , amountCurrency and destination currency and Table2 have the conversion rate(As mentioned in image conversation rate are in terms of USD) .
What I want to do :
Convert Amount from amountCurrency to destination currency and update it in last column of Table1
Example : Amount in row one of Table1 is in INR and I want to convert it to CAD. As per math I will get conversation rate for 1 INR on given conversion_date from Table2 multiple it by AmountCurrency. something like,
select Rate from Table1 where converstion_Date = '2014-06-30' and Currency = 'INR'.
While (Select Count(*) From Table1) > 0
// step1 : Get top row
//step2 : Get conversition rate for **Amountcurrency** using select query to table2
//step3 : Multiply with amount (Here we have USD value for amount)
//step4: Get conversion rate for **DestinationCurrency**
//step5: Divide USD Amount with result from step 4
All you need is one query with an instance of your base table and two instances of your conversion table, one joined on
base.amountCurrency = rate.currency and the other joined on
base.destinationCurrency = rate.currency. If you need to add
conversion_date criteria in, you can do that, too, if you have multiple rates for each currency over time and you want the most recent one, or whichever.
select b.*, a.rate as rate_amount, d.rate as rate_destination, amount * a.rate / d.rate as amtInDestCurrency from base b inner join rate a on b.amountcurrency = a.currency and b.conversion_date = a.conversion_date inner join rate d on b.destinationCurrency = d.currency and b.conversion_date = d.conversion_date