I'm working on exchange rate functionality that will convert values in a table based on date specified on each line of data. I need to link my data table to exchange rate table on CURRENCY and DATE fields. The issue that I'm facing is that my exchange rate table contains a date range instead of a single date. More specifically, I have an exchange rate for a currency with effective date of say April 1, 2016 and an expiration date of April 30, 2016. Other currencies have effective date of January 1, 2016 and an expiration date of December 31, 2016.
Does anyone have any idea of how to link a Fact table to Exchange Rates table on currency and date when date within Fact table is a single point while dates within Exchange Rates table are ranges? Attached is an example. Any help is much appreciated.
Mikhail B.
