SQL join against date ranges?

date-range, join, sql, sql-server, t-sql

Solution

You could first do a self-join on the exchange rates which are ordered by date so that you have the start and the end date of each exchange rate, without any overlap or gap in the dates (maybe add that as view to your database - in my case I'm just using a common table expression).

Now joining those "prepared" rates with the transactions is simple and efficient.

Something like:

WITH IndexedExchangeRates AS (           
            SELECT  Row_Number() OVER (ORDER BY Date) ix,
                    Date,
                    Rate 
            FROM    ExchangeRates 
        ),
        RangedExchangeRates AS (             
            SELECT  CASE WHEN IER.ix=1 THEN CAST('1753-01-01' AS datetime) 
                    ELSE IER.Date 
                    END DateFrom,
                    COALESCE(IER2.Date, GETDATE()) DateTo,
                    IER.Rate 
            FROM    IndexedExchangeRates IER 
            LEFT JOIN IndexedExchangeRates IER2 
            ON IER.ix = IER2.ix-1 
        )
SELECT  T.Date,
        T.Amount,
        RER.Rate,
        T.Amount/RER.Rate ConvertedAmount 
FROM    Transactions T 
LEFT JOIN RangedExchangeRates RER 
ON (T.Date > RER.DateFrom) AND (T.Date <= RER.DateTo)

Notes:

You could replace `GETDATE()` with a date in the far future, I'm assuming here that no rates for the future are known.

Rule (B) is implemented by setting the date of the first known exchange rate to the minimal date supported by the SQL Server `datetime`, which should (by definition if it is the type you're using for the `Date` column) be the smallest value possible.

Problem

Consider two tables: Transactions, with amounts in a foreign currency: ``` Date Amount ========= ======= 1/2/2009 1500 2/4/2009 2300 3/15/2009 300 4/17/2009 2200 etc. ``` ExchangeRates, with the value of the primary currency (let's say dollars) in the foreign currency: ``` Date Rate ========= ======= 2/1/2009 40.1 3/1/2009 41.0 4/1/2009 38.5 5/1/2009 42.7 etc. ``` Exchange rates can be entered for arbitrary dates - the user could enter them on a daily basis, weekly basis, monthly basis, or at irregular intervals. In order to translate the foreign amounts to dollars, I need to respect these rules: A. If possible, use the most recent previous rate; so the transaction on 2/4/2009 uses the rate for 2/1/2009, and the transaction on 3/15/2009 uses the rate for 3/1/2009. B. If there isn't a rate defined for a previous date, use the earliest rate available. So the transaction on 1/2/2009 uses the rate for 2/1/2009, since there isn't an earlier rate defined. This works... ``` Select t.Date, t.Amount, ConvertedAmount=( Select Top 1 t.Amount/ex.Rate From ExchangeRates ex Where t.Date > ex.Date Order by ex.Date desc ) From Transactions t ``` ... but (1) it seems like a join would be more efficient & elegant, and (2) it doesn't deal with Rule B above. Is there an alternative to using the subquery to find the appropriate rate? And is there an elegant way to handle Rule B, without tying myself in knots?

Original source