title: MAX Date <= to Date in another table
body: <p>We store exchange rates in a table and it is updated a few times a week for the latest exchange rates.  I am trying to be able to take a date on a sales order, and assign it the exchange rate that was active at the time of the order.  Below is an example of a listing of exchange rates and their effective dates, as well as sales orders, and what should be the effect rate (Exchage rate @ time of order, which is what I want to pull in).</p>
  
  <pre><code>currate.effectivedate      currate.currentrate
  12/2/2012                  0.55
  12/7/2012                  0.52
  12/13/2012                 0.54
  12/19/2012                 0.53
  12/22/2012                 0.56
  
  orderhed.orderdate        orderhed.ordernum     Exchage Rate @ date of order
  12/4/2012                  105                  0.55
  12/8/2012                  111                  0.52
  12/9/2012                  116                  0.52
  12/19/2012                 120                  0.53
  12/28/2012                 123                  0.56
  </code></pre>
  
  <p>I have below an example of what I have been trying to do, but its not working.  First, I can’t think of how to bring the currate.currentrate field in because of the MAX function on currate.effectivedate.  Then for t_rate.effectivedate, I am getting just the MAX day of the exchange rate of the table period, not the max date that is &lt;= the orderhed.orderdate.  I need the MAX currate.effectivedate that is less than or equal to the orderhed.orderdate in order to get the effective date (and corresponding currentrate.currentrate) that was in effect at the time of the order.  I am sorry if I am repeating myself, or if it doesn’t make sense.  If it isn’t obvious by my sad little query below, I am extremely new to writing queries so any help I can get is greatly appreciated. </p>
  
  <pre><code>SELECT     
  orderhed.ordernum, 
  orderhed.orderdate, 
  orderdtl.orderline, 
  orderdtl.unitprice, 
  orderdtl.docunitprice, 
  orderdtl.discount, 
  orderdtl.docdiscount, 
  t_rate.effectivedate
  
  FROM         orderhed LEFT OUTER JOIN
  
  (SELECT     rate1.company, MAX(rate1.effectivedate) AS effectivedate, order1.orderdate
  FROM         currrate as rate1 INNER JOIN
                    orderhed AS order1 ON rate1.company = order1.company
  WHERE     (rate1.company = 'lotcol') AND (rate1.currencycode = 'usd') 
  GROUP BY rate1.company, order1.orderdate) 
  
  AS t_rate ON orderhed.company = t_rate.company AND t_rate.orderdate &lt;= orderhed.orderdate 
                          INNER JOIN
                    orderdtl ON orderhed.company = orderdtl.company 
  WHERE     (orderhed.company = 'lotcol') 
  </code></pre>
  
  <p>Update--- </p>
  
  <p>thanks @LastCoder you answer is what nudged me in the right direction. At the end of the day, this is exactly what worked: </p>
  
  <pre><code>SELECT     orderhed.ordernum, orderhed.orderdate, orderdtl.orderline, orderdtl.unitprice, orderdtl.docunitprice, orderdtl.discount, orderdtl.docdiscount, t_rate.effectivedate, 
                    t_rate.ordernum AS Expr1, t_rate1.currentrate
  FROM         orderhed INNER JOIN
                        (SELECT     rate1.company, MAX(rate1.effectivedate) AS effectivedate, order1.orderdate, order1.ordernum
                          FROM          currrate AS rate1 INNER JOIN
                                                 orderhed AS order1 ON rate1.company = order1.company AND rate1.effectivedate &lt;= order1.orderdate
                          WHERE      (rate1.company = 'lotcol') AND (rate1.currencycode = 'usd')
                          GROUP BY rate1.company, order1.orderdate, order1.ordernum) AS t_rate ON orderhed.ordernum = t_rate.ordernum AND orderhed.company = t_rate.company AND 
                    t_rate.orderdate &lt;= orderhed.orderdate INNER JOIN
                        (SELECT     company, currencycode, effectivedate, currentrate
                          FROM          currrate AS currrate_1
                          WHERE      (company = 'lotcol') AND (currencycode = 'usd')) AS t_rate1 ON t_rate.company = t_rate1.company AND 
                    t_rate.effectivedate = t_rate1.effectivedate INNER JOIN
                    orderdtl ON orderhed.company = orderdtl.company AND orderhed.ordernum = orderdtl.ordernum
  WHERE     (orderhed.company = 'lotcol')
  ORDER BY orderhed.ordernum
  </code></pre>