Multiple Currency Conversions in Single Query

0
votes

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.

2
sqlsql-server
It looks like you have only shown parts of each of the tables, otherwise how did you know the third transaction whose native currency is euros converts to British pounds? No such conversion rate in your table. – Chris Maurer
Its just a sample data. Updated the data – user2293950
what is local_price() ? Can you also show this ? – Squirrel
I don't see any USD to EUR in the ExchangeRate table. Do you have all possible permutation for the required result ? – Squirrel
Is the sample data realistic ? You had USD - GBP - 0.9 and EUR - GP - 0.9. So USD - EUR - 1.0 ? ? – Squirrel

2 Answers

1
votes

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

enter image description here

Transaction Table

enter image description here

Result Table

enter image description here

0
votes

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;