title: UPDATE/JOIN using date ranges
body: <p>Hello I am trying to do an update/join on two tables based on a few conditions.  Here are the structures of my two tables:</p>
  
  <p>Table 1</p>
  
  <pre><code>Pricelist     Product     Price     Effective Date     Expiry Date
  1             1           NULL      2012-07-01         2013-06-30
  1             2           NULL      2012-07-01         2013-06-30
  1             3           NULL      2012-07-01         2013-06-30   
  </code></pre>
  
  <p>Table 2</p>
  
  <pre><code>Pricelist     Product     Price     As of Date
  1             1           50.00     2013-06-26
  1             1           55.00     2013-07-02
  1             2           77.00     2013-06-26
  1             2           108.00    2013-07-02
  1             3           250.00    2013-06-29 
  1             3           263.00    2013-06-30
  1             3           266.00    2013-07-01
  </code></pre>
  
  <p>etc.</p>
  
  <p>As you might imagine, I need the resultant table to look like this:</p>
  
  <p>Table 3</p>
  
  <pre><code>Pricelist    Product      Price     Effective Date     Expiry Date
  1            1            50.00     2012-07-01         2013-06-30
  1            2            77.00     2012-07-01         2013-06-30
  1            3            263.00    2012-07-01         2013-06-30
  </code></pre>
  
  <p>The conditions apart from joining on Pricelist and Product, is that the "As of Date" in Table 2 must be the less than or equal to the "Expiry Date" in Table 1.</p>
  
  <p>Here is my code so far:</p>
  
  <pre><code>UPDATE A
  SET A.[Price] = B.[Price]
  FROM [Table1] A 
  INNER JOIN [Table2] B
  ON A.[Pricelist] = B.[Pricelist]
  AND A.[Product] = B.[Product]
  HAVING MAX(B.[as_of_DATE]) &lt;= A.[Expired Date]
  </code></pre>
  
  <p>And of course this is wrong, I just am unsure of the syntax to get it to work.  Help is much appreciated in advance.  Cheers!</p>