SQL : Currency Conversion from given currency to d

Question

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'.

Above query will give me 0.0160752000 and we will convert INR TO USD i.e 100 * 0.0160752000 = 1.60752 USD
Since we want to convert it to CAD get conversion rate for 1 CAD on given conversion_date, 1 CAD = 0.9399380000 USD, now we need to convert 1.60752 USD to CAD that can be done by dividing it with CAD rate i.e 1.60752/1.60752 = 1.71024 CAD.

My Table1 has around 10000 rows and Table2 has conversion rate for all the currencies for all dates till now. What is the best way to iterate Table1 rows and do the conversion and update it in CalculateAmountInDestinationCurrency column.

I was thinking to have a loop like,

While (Select Count(*) From Table1) > 0
begin

// 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    

//Update   

end

Any help is appreciated. Is this good way to do this? Is there any better way?

Answer 1

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.

something like:

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