Multiple Currency Conversions in Single Query

Question

Please find the below sample data. There are 2 tables Transaction and Exchange rate. If i need to convert the transactions table to USD,GBP and EUR then below is the query which i have tried but it doesnt give the required output.

Transaction

Currency	Local_Price
USD	1000
GBP	100
EUR	10
USD	100

ExchangeRate

From_Currency	To_Currency	Exchange_Rate
USD	GBP	0.9
EUR	USD	1.1
GBP	USD	1.3
EUR	GBP	0.9

Expected Output

Currency	Local_Price	In_USD	In_GBP	In_Eur
USD	1000	1000	900	80
GBP	100	130	100	99
EUR	10	11	9	10
USD	100	100	90	98

Query

Select Currency,Local_price, local_price(coalesce(Exchange_Rate,1))  as In_USD,
from Transaction T
left join ExchangeRate ER on T.Currency=ER.From_Currency 
                         and To_Currency in ('USD','GBP','EUR')

I do not have data from USD to USD or EUR to EUR or GBP to GBP in my exchange rate table.

Please help me with the query for required output.

Answer 1

I would suggest three joins. Assuming you have exchange rates for all currency pairs (apart from the identity):

select t.currency, t.local_price,
       coalesce(er_usd.exchange_rate, 1) * t.local_price as in_usd,
       coalesce(er_eur.exchange_rate, 1) * t.local_price as in_eur,
       coalesce(er_gbp.exchange_rate, 1) * t.local_price as in_gbp
from transactions t left join
     exchange_rates er_usd
     on t.currency = er_usd.from_currency and
        er_usd.to_currency = 'USD' left join
     exchange_rates er_eur
     on t.currency = er_usd.from_currency and
        er_usd.to_currency = 'EUR' left join
     exchange_rates er_gbp
     on t.currency = er_usd.from_currency and
        er_usd.to_currency = 'GBP' ;

You can also do this using conditional aggregation in the exchange table:

select t.*,
       er.to_usd * local_price as in_usd,
       er.to_eur * local_price as in_eur,
       er.to_gbp * local_price as in_gbp
from transactions t left join
     (select from_currency,
             (case when from_currency = 'USD'
                   then max(case when to_currency = 'USD' then exchange_rate end)
              end) as to_usd,
             (case when from_currency = 'EUR'
                   then max(case when to_currency = 'EUR' then exchange_rate end)
              end) as to_eur,
             (case when from_currency = 'GBP'
                   then max(case when to_currency = 'GBP' then exchange_rate end)
              end) as to_gbp
       from exchange_rates er
       group by from_currency
      ) er
      on er.from_currency = t.currency;

Answer 2

I have entered the answer to your question below. Just be sure to enter all the currency conversions in the ExchangeRate table. Which can be fetched if needed.

I have also entered the tables used and the output result.

select Currency,Local_Price,
CASE
    WHEN Currency = 'USD' THEN Local_Price
    WHEN Currency = 'GBP' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'GBP' AND To_Currency = 'USD') * Local_Price 
    WHEN Currency = 'EUR' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'EUR' AND To_Currency = 'USD') * Local_Price 
END AS In_USD,
CASE
    WHEN Currency = 'USD' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'USD' AND To_Currency = 'GBP') * Local_Price
    WHEN Currency = 'GBP' THEN Local_Price 
    WHEN Currency = 'EUR' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'EUR' AND To_Currency = 'GBP') * Local_Price 
END AS In_GBP,
CASE
    WHEN Currency = 'USD' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'USD' AND To_Currency = 'EUR') * Local_Price
    WHEN Currency = 'GBP' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'GBP' AND To_Currency = 'EUR') * Local_Price 
    WHEN Currency = 'EUR' THEN Local_Price 
END AS In_Eur

from [Transaction]

ExchangeRate Table

Transaction Table

Result Table