SQL join against date ranges?

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?

Edit Report

1 Answer

There's nothing about a join that will be more elegant than the TOP 1 correlated subquery in your original post. However, as you say, it doesn't satisfy requirement B.

These queries do work (SQL Server 2005 or later required). See the SqlFiddle for these.

SELECT
   T.*,
   ExchangeRate = E.Rate
FROM
  dbo.Transactions T
  CROSS APPLY (
    SELECT TOP 1 Rate
    FROM dbo.ExchangeRate E
    WHERE E.RateDate <= T.TranDate
    ORDER BY
      CASE WHEN E.RateDate <= T.TranDate THEN 0 ELSE 1 END,
      E.RateDate DESC
  ) E;

Note that the CROSS APPLY with a single column value is functionally equivalent to the correlated subquery in the SELECT clause as you showed. I just prefer CROSS APPLY now because it is much more flexible and lets you reuse the value in multiple places, have multiple rows in it (for custom unpivoting) and lets you have multiple columns.

SELECT
   T.*,
   ExchangeRate = Coalesce(E.Rate, E2.Rate)
FROM
  dbo.Transactions T
  OUTER APPLY (
    SELECT TOP 1 Rate
    FROM dbo.ExchangeRate E
    WHERE E.RateDate <= T.TranDate
    ORDER BY E.RateDate DESC
  ) E
  OUTER APPLY (
    SELECT TOP 1 Rate
    FROM dbo.ExchangeRate E2
    WHERE E.Rate IS NULL
    ORDER BY E2.RateDate
  ) E2;

I don't know which one might perform better, or if either will perform better than other answers on the page. With a proper index on the Date columns, they should zing pretty well--definitely better than any Row_Number() solution.

answered 2013-02-11T07:28:55.857

SQL join against date ranges?

1 Answer

Your Answer