title: Efficient way to get max date before a given date
body: <p>Suppose I have a table called Transaction and another table called Price. Price holds the prices for given funds at different dates. Each fund will have prices added at various dates, but they won't have prices at all possible dates. So for fund XYZ I may have prices for the 1 May, 7 May and 13 May and fund ABC may have prices at 3 May, 9 May and 11 May.</p>
  
  <p>So now I'm looking at the price that was prevailing for a fund at the date of a transaction. The transaction was for fund XYZ on 10 May. What I want, is the latest known price on that day, which will be the price for 7 May.</p>
  
  <p>Here's the code:</p>
  
  <pre><code>select d.TransactionID, d.FundCode, d.TransactionDate, v.OfferPrice
  from Transaction d
      inner join Price v
          on v.FundCode = d.FundCode
          and v.PriceDate = (
              select max(PriceDate)
              from Price
              where FundCode = v.FundCode
              /* */ and PriceDate &lt; d.TransactionDate 
          )
  </code></pre>
  
  <p>It works, but it is very slow (several minutes in real world use). If I remove the line with the leading comment, the query is very quick (2 seconds or so) but it then uses the latest price per fund, which is wrong.</p>
  
  <p>The bad part is that the price table is minuscule compared to some of the other tables we use, and it isn't clear to me why it is so slow. I suspect the offending line forces SQL Server to process a Cartesian product, but I don't know how to avoid it.</p>
  
  <p>I keep hoping to find a more efficient way to do this, but it has so far escaped me. Any ideas?</p>