title: SQL join against date ranges?
body: <p>Consider two tables: </p>
  
  <p><strong>Transactions</strong>,  with amounts in a foreign currency:</p>
  
  <pre><code>     Date  Amount
  ========= =======
   1/2/2009    1500
   2/4/2009    2300
  3/15/2009     300
  4/17/2009    2200
  etc.
  </code></pre>
  
  <p><strong>ExchangeRates</strong>, with the value of the primary currency (let's say dollars) in the foreign currency:</p>
  
  <pre><code>     Date    Rate
  ========= =======
   2/1/2009    40.1
   3/1/2009    41.0
   4/1/2009    38.5
   5/1/2009    42.7
  etc.
  </code></pre>
  
  <p>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. </p>
  
  <p>In order to translate the foreign amounts to dollars, I need to respect these rules: </p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>This works...</p>
  
  <pre><code>Select 
      t.Date, 
      t.Amount,
      ConvertedAmount=(   
          Select Top 1 
              t.Amount/ex.Rate
          From ExchangeRates ex
          Where t.Date &gt; ex.Date
          Order by ex.Date desc
      )
  From Transactions t
  </code></pre>
  
  <p>... but (1) it seems like a join would be more efficient &amp; elegant, and (2) it doesn't deal with Rule B above.</p>
  
  <p>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?</p>