title: SQL tricks: finding a value within a range across rows
body: <p>I am using a database of exchange rates that has values valid only within specific dates.  For instance if I want to convert USD to EURO I have to use a particular exchange rate that falls within specific dates.  The exchange rate changes over time and it will eventually be replaced by another, more updated one.  Here is a sample of the exchange rate database I have:</p>
  
  <pre><code>Exchange_Rate_History
  Valid-From  Exchange-rate  From-Currency  To-Currency
  2012-04-16  0.8            USD            EUR
  2012-04-18  0.82           USD            EUR
  2012-04-20  0.81           USD            EUR  
  </code></pre>
  
  <p>Now if you notice, I only have a 'Valid-From' date but I DO NOT HAVE a 'Valid-To' date. </p>
  
  <p>Now I have another table that needs to be joined to the Exchange_Rate_History table.  This table contains shopping transactions</p>
  
  <pre><code>Purchases
  Transaction-ID  Transaction-Date  Amount-In-USD
  1               2012-04-16        100
  2               2012-04-17        100
  </code></pre>
  
  <p>For the above two transactions we have two different dates, 16th and 17th April 2012.  BUT for both dates we need to use the exchange rate that is marked as being Valid-From 2012-04-16.  Since I only have a from date I cannot use a BETWEEN..AND to perform the join.  The following join is therefore not possible</p>
  
  <pre><code>SELECT 
      * 
  FROM
      Exchange_Rate_History
  INNER JOIN Purchases ON (Purchases.Transaction-Date BETWEEN Exchange_Rate_History.Valid-From AND Exchange_Rate_History.???)
  </code></pre>
  
  <p>I was thinking of doing a self-join (recursive/self-referencing relationship) of the Exchange_Rate_History table so that I get two Valid-From columns next to each other.  The first Valid-From would be the original one while the second would be a vertically shifted one.  The resulting table would look like this:</p>
  
  <pre><code>Exchange_Rate_History
  Valid-From  Exchange-rate  From-Currency  To-Currency Valid-From-1 (aliased to Valid-To)  
  2012-04-16  0.8            USD            EUR         2012-04-18
  2012-04-18  0.82           USD            EUR         2012-04-20
  2012-04-20  0.81           USD            EUR  
  </code></pre>
  
  <p>I would want to use the Valid-From-1 field as if it were a Valid-To field so that I can perform the SQL statement above. Notice now that the Valid-From field has the date 16th April while the Valid-To has the 18th April.   However at this stage I do not know how to do the recursive relationship that 'shifts' the records vertically!</p>
  
  <p>Any help please?  This is not an easy one!</p>