Search code examples
mysqldaterate

Select Exchange Rate based on Currency and Date


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)

Solution

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