MAX Date <= to Date in another table

Question

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).

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

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 <= 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.

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 <= orderhed.orderdate 
                        INNER JOIN
                  orderdtl ON orderhed.company = orderdtl.company 
WHERE     (orderhed.company = 'lotcol') 

Update---

thanks @LastCoder you answer is what nudged me in the right direction. At the end of the day, this is exactly what worked:

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 <= 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 <= 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

Answer 1

SELECT o.orderdate
    , ISNULL(r.effectivedate, (
        SELECT MAX(effectivedate) FROM t_rate WHERE effectivedate < o.orderdate
    )
FROM orderhed o
LEFT JOIN t_rate r ON o.orderdate = r.effectivedate

There's a minimal solution to your issue. You need to match the "orderdate" to the "effectivedate" that is equal to or closest less than it.