title: How can I get the max date (per primarykey) and get its difference with the one from another table?
body: <p>I have this sample data below (table1):</p>
  
  <pre><code>PKEY   DATE
  ----   ----
  ABC1   2/13
  CFG5   1/05
  POK9   1/10
  </code></pre>
  
  <p>and I have these dates from another table (table2):</p>
  
  <pre><code>PKEY   REF#   DATE
  ----   ----   ----
  1      ABC1   2/14
  2      ABC1   2/20
  3      ABC1   3/01
  4      CFG5   3/05
  5      CFG5   2/10
  6      ABC1   2/25
  </code></pre>
  
  <p>I want to get the <strong>max</strong> date from <strong>table2</strong> (<strong>per REF#</strong>) and get the <strong>difference</strong> with the date from <strong>table1</strong>. showing the below result. </p>
  
  <pre><code>REF#   DIFF   
  ----   ----
  POK9    55
  CFG5    54
  ABC1    21  [ *note: this is equal to datediff(day,table1.DATE,getdate()) ]
  </code></pre>
  
  <p>*ALSO, i want to use the date <strong>today</strong> (to be deducted with the date from table1) if REF# is not existing in table2 (like the 3rd row in the above example).  Hope this makes sense.. let me know if there's anything unclear so I can edit right away..</p>
  
  <p>**UPDATE:
  this is what i've tried so far.. but still incomplete.. thanks for the hardwork</p>
  
  <pre><code>select t1.[REF#]
  from table1 as t1
  join table2 as t2
  on t1.[REF#] COLLATE DATABASE_DEFAULT = t2.[REF#] COLLATE DATABASE_DEFAULT
  </code></pre>