I have a table of payments in various currencies that needs conversion to local currency.
tblPayments table looks like (PaymentID PK)
Conversion rates are provided by tblCurrencyHistory where PK is a composite key of CurrencyCode and CurrencyDate. (ie, for each currency, we only have one currency rate per day.)
tblCurrencyHistory
We are not guaranteed to have currency rates for all transaction dates, so we need the previous rate available.
In order words, we need Max(CurrencyDate) WHERE CurrencyDate<=TransactionDate.
In the SELECT statement I need to provide the payments and selected currency rate and date.
Should be simple but I can't get my head around it. Any help much appreciated.
tblPayments table looks like (PaymentID PK)
PaymentID | TransactionDate | CurrencyCode | PaymentAmount |
17 | 4/3/2023 | EUR | 512.00 |
Conversion rates are provided by tblCurrencyHistory where PK is a composite key of CurrencyCode and CurrencyDate. (ie, for each currency, we only have one currency rate per day.)
tblCurrencyHistory
CurrencyCode | CurrencyDate | CurrencyRate |
EUR | 5/4/2023 | 1.140821 |
We are not guaranteed to have currency rates for all transaction dates, so we need the previous rate available.
In order words, we need Max(CurrencyDate) WHERE CurrencyDate<=TransactionDate.
In the SELECT statement I need to provide the payments and selected currency rate and date.
Should be simple but I can't get my head around it. Any help much appreciated.