Find the most recent row where no exact match on date is found

Question

I am trying to write a query that tries to extract the value of the exchange rate where:

• Query date = the exchange date; and
• Query currency = the specified currency

If it does not contain a matching value like on 5-Sep-2015, it should extract the value from the last date that contains a value. In this case, the value should be taken from 4-Sep-2015:

 Create Table ExchRate
 (  Exch_ID int,
    Currency varchar(3),
    Exch_value numeric(14,7),
    Exch_date datetime)

Insert INTO ExchRate 
Values (1, 'SGD', 1.4173000, '2015-09-04 00:00:00'),
       (2, 'SGD', 1.4240000, '2015-09-07 00:00:00'),
       (3, 'SGD', 1.4291500, '2015-09-08 00:00:00'),
       (4, 'EUR', 0.8984700, '2015-09-04 00:00:00'),
       (5, 'EUR', 0.8955700, '2015-09-07 00:00:00'),
       (6, 'EUR', 0.8957000, '2015-09-08 00:00:00')

The result should be:

Currency   Exch_Date   Exch_Value
SGD        2015-09-05  1.4173000

Secondly, what if I am joining from another table to calculate the total cost in USD? What kind of SQL statement should I be using? In this SQLFiddle example, it should have exchange rate value for every PO with different PO dates.

Answer 1

How about the following query? This query will take the exchange on the query date if it exists. If not, it will find the most recent exchange date prior to the query date and use that date's exchange rate.

DECLARE @Query_currency VARCHAR(3) = 'SGD',
        @Query_date DATETIME = '2015-09-05 00:00:00'
SELECT TOP(1) Currency, Exch_date AS Observed_exch_date,
    @Query_date AS Exch_date, Exch_value
FROM ExchRate
-- Find the exchange rate on the query date for this currency
-- If there isn't one, find the most recent exchange rate
WHERE Currency = @Query_currency
    AND Exch_date <= @Query_date
ORDER BY Observed_exch_date DESC

Looking up exchange rates in batch

If you need to find the exchange rate for multiple rows at a time, you can use CROSS APPLY to find the most recent exchange rate for each row. If you make sure that you have an appropriate index on (Currency, Exch_date) that includes the Exch_value column, looking up the exchange rate for each row will perform a single seek with no additional sort needed. You can see a full demonstration in this SQL Fiddle.

SELECT p.PO_Num, p.PO_Cur, p.PO_Date, e.Exch_date, e.Exch_value
FROM PO p
CROSS APPLY (
    -- For each PO, seek to the current or most recent exchange rate
    SELECT TOP 1 ex.Exch_date, ex.Exch_value
    FROM ExchRate ex
    WHERE ex.Currency = p.PO_Cur
      AND ex.Exch_date <= p.PO_Date
    ORDER BY ex.Exch_date DESC
) e