Select Exchange Rate based on Currency and Date

Question

I've looked around for a solution to this, but at least I was unable to find anything which would at least be similar to my case.

I need to select the exchange rate, based on the date a product was purchased. Let me try and explain.

I have a table with Currencies:

CREATE TABLE `tblCurrencies` (
  `CurrID` int(11) NOT NULL AUTO_INCREMENT,
  `CurencySymbol` varchar(1) DEFAULT NULL,
  `CurrencyCode` varchar(3) DEFAULT NULL,
  `CurrencyDescription` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`CurrID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

A table with Exchange Rates:

CREATE TABLE `tblExchRates` (
  `ExcID` int(11) NOT NULL AUTO_INCREMENT,
  `CurrKey` int(11) DEFAULT NULL,
  `Date` date DEFAULT NULL,
  `Exchange` decimal(11,3) DEFAULT NULL,
  PRIMARY KEY (`ExcID`),
  KEY `CurrKey` (`CurrKey`),
  CONSTRAINT `tblExchRates_ibfk_1` FOREIGN KEY (`CurrKey`) REFERENCES `tblCurrencies` (`CurrID`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8;

And a table with Products (note my products are listed in numbers in the table, which is OK in my case):

CREATE TABLE `tblProducts` (
  `ProductID` int(11) NOT NULL AUTO_INCREMENT,
  `Contract` int(11) DEFAULT NULL,
  `Product` int(11) DEFAULT NULL,
  `Type` varchar(100) DEFAULT NULL,
  `Currency` int(11) DEFAULT NULL,
  `Amount` decimal(10,0) DEFAULT NULL,
  `PurchaseDate` datetime DEFAULT NULL,
  PRIMARY KEY (`ProductID`),
  KEY `Contract` (`Contract`),
  KEY `Currency` (`Currency`),
  CONSTRAINT `tblShopCart_ibfk_2` FOREIGN KEY (`Currency`) REFERENCES `tblCurrencies` (`CurrID`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `tblShopCart_ibfk_1` FOREIGN KEY (`Contract`) REFERENCES `tblContracts` (`ContractID`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3155 DEFAULT CHARSET=utf8;

In the Exchange Rates table, as an example, values are set like this:

CurrKey              Date                 Exchange
1                    15-01-2017           0.850
1                    31-01-2017           0.856
1                    02-02-2018           0.918
1                    18-02-2018           0.905
2                    04-02-2018           1.765
2                    14-02-2018           1.755

And so on...

I want to have a query that select a unique exchange rate based on the date a product was purchased and the currency it was purchased.

In other words, as an example, if I have a product that was purchased on 07-02-2018, the query has to select the exchange rate which is valid in the relevant date rage that matches the purchase date and its currency. In this example, the correct exchange rage for a product purchased on 07-02-2018 which has a currkey of 1 would be 0.918

Please note that exchange rates are set on random dates (as per example above).

I managed to make this query, but it is not precise, as it sometimes returns two or more results (due to the 10 days range I set), whereas I only need 1 result:

SELECT
tblExchRates.Exchange
FROM
tblCurrencies
INNER JOIN tblExchRates ON tblExchRates.CurrKey = tblCurrencies.CurrID
WHERE
tblCurrencies.CurrencyCode = "EUR" AND
tblExchRates.Date BETWEEN (tblProducts.PurchaseDate - INTERVAL 10 DAY) AND (tblProducts.PurchaseDate + INTERVAL 10 DAY)

Answer 1

I am a beginner myself so no guarantees on correctness. I believe you have to use a certain application programming language along with SQL, for example PHP. Still, I will outline the basic steps I would take.

---

1. Assign the purchase currency ID and purchase date to variables using a simple SELECT statement. Assume I give the ID to targetID and date to targetDate.
2. SELECT MIN(Date) FROM tblExchRates WHERE Date <= targetDate AND CurrKey =targetID; //Select most recent date whose range includes the date of purchase among the matching currency IDs. Assign the result of this statement to another variable. Assume I used the variable dateRange.
3. SELECT Exchange FROM tblExchRates WHERE Date = targetDate; //Find the exchange rate of the selected date.

---

Note that there are many ways to do this. For example, you could use table JOINS (refer to this link: https://www.w3schools.com/sql/sql_join.asp ) or select columns from different tables in just one SQL statement (refer to this Stack overflow question: MySQL Select all columns from one table and some from another table). Last, you can use SQL to create variables (refer to this question: Set the variable result, from query) and then perform operations.

Answer 2

For a fairly simple solution you can do

SELECT 
    p.*
    ,(SELECT TOP 1 er.Exchange
     FROM tblExchRates AS er
     INNER JOIN tblCurrencies AS c ON er.CurrKey = c.CurrID AND c.CurrencyCode = 'EUR'
     WHERE er.Date <= p.PurchaseDate
     ORDER BY er.Date DESC) AS ExchangeRate
FROM
     tblProducts AS p

Another option, if you have control over the schema then changing your exchange rates table to have a DateFrom and DateTo rather than just a date would then mean you can simply find the correct exchange rate using the BETWEEN keyword.