Currency Conversion

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

Expected Output

Currency Local_Price In_USD
USD 1000 1000
GBP 100 130
EUR 10 11
USD 100 100

Query Select Currency,Local_price, Local_Price*Exchange_Rate as In_USD from Transaction T left join ExchangeRate ER on T.Currency=ER.From_Currency and To_Currency='USD'

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.

1
sqlsql-server

1 Answers

1
votes

Use coalesce():

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 = 'USD';