title: Get max of two dates from two different tables in teradata - scenario?
body: <p>I have two tables table1 and table2. Table2 is having less number of rows than table1. In these two tables there are two date columns caldate1 in table1 and caldate2 in table2. So now I need to join on these two tables and get the maximum of the two date columns and keep that in new table. But if we inner join on these two tables the table1 rows which are not there in table2 will not go into the final table. So we need some thing like </p>
  
  <pre><code>table1 
  left outer join
  table2
  </code></pre>
  
  <p>But there is a situation that the two dates are having nulls. So Can I use coalesce to get the correct data in the below scenarios..
  <p>1. row in table1 is not there in table2 -> then the caldate1 in table1 should go into final table.
  <p>2. row in table1 is there in table2 and the caldate1 of table1 and caldate2 of table2 are nulls -> then null should come into final table's date column
  <p>3. row in table1 is there in table2 and caldate1 is not null and caldate2 is null -> then caldate1 should come into final table.
  <p>4. row in table1 is there in table2 and caldate1 is null and caldate2 is not null -> then caldate2 should come into final table
  <p>5. row in table1 is there in table2 and caldate1 is greater than caldate2 -> caldate1 should come into final table
  <p>6. row in table1 is there in table2 and caldate2 is greater than caldate1 -> caldate2 should come into final table
  <p>We dont need to consider the rows in table2 which are not matching with table1. So basically i need all table1 rows with latest of the caldate if a particular row is there in both tables. Thanks in advance. I am not able to get correct function to do it.Is it coalesce?</p>