I have a situation to calculate sales amount based on the last exchange rate. Below are the tables.
Sales Table contains “TransactionDate”, “Amount” and “TransactionCurrency”
Exchange rate table which contains Date, FromCurrency, ToCurrency and ExchangeRate.
The issues is I can’t make a relationship between Sales and Exchange Rate table, I would have done it if the exchange rate would have only one column for currency but in my case I have exchange rate based on FromCurrency and ToCurrency. I have to check exchange rate in the exchange rate table in both the columns i.e. FromCurrency and ToCurrency based on the currency in the sales table.