title: SQL Server : max date from two tables
body: <p>I need to find the last time a customer ordered an item for all customers and all items. I figured I would start with the last time the customer ordered anything. This would involve two tables <code>oeordhdr_sql</code> and <code>oehdrhst_sql</code> both have <code>cus_no</code> and <code>ord_dt</code>.</p>
  
  <p>I am getting an error on the <code>on</code></p>
  
  <pre><code>select 
      arcusfil.cus_no, Order_Date.orddt
  from 
      arcusfil_sql arcusfil
  join 
      (SELECT 
           cus_no, MAX(MaxDate) AS orddt 
       FROM 
           (SELECT 
                cus_no, MAX(ord_dt) AS MaxDate 
            FROM 
                oeordhdr_sql 
            GROUP BY
                cus_no
  
            UNION
  
            SELECT 
                cus_no, MAX(ord_dt) AS MaxDate 
            FROM 
                oehdrhst_sql 
            GROUP BY 
                cus_no) Order_date ON arcusfil.cus_no = Order_date.cus_no
  </code></pre>