How can I get the max date (per primarykey) and ge

Question

I have this sample data below (table1):

PKEY   DATE
----   ----
ABC1   2/13
CFG5   1/05
POK9   1/10

and I have these dates from another table (table2):

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

I want to get the max date from table2 (per REF#) and get the difference with the date from table1. showing the below result.

REF#   DIFF   
----   ----
POK9    55
CFG5    54
ABC1    21  [ *note: this is equal to datediff(day,table1.DATE,getdate()) ]

*ALSO, i want to use the date today (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..

**UPDATE: this is what i've tried so far.. but still incomplete.. thanks for the hardwork

select t1.[REF#]
from table1 as t1
join table2 as t2
on t1.[REF#] COLLATE DATABASE_DEFAULT = t2.[REF#] COLLATE DATABASE_DEFAULT

Answer 1

Something like this?

select a.pkey
      ,datediff(day, max(a.date), coalesce(max(b.date), getdate())) as diff 
  from table1      a
  left join table2 b on(b.ref# = a.pkey)
 group 
    by a.pkey;

Edited for comments: You should be able to filter like this:

select a.pkey
      ,datediff(day, max(a.date), coalesce(max(b.date), getdate())) as diff 
  from table1      a
  left join table2 b on(b.ref# = a.pkey)
 group 
    by a.pkey
having datediff(day, max(a.date), coalesce(max(b.date), getdate())) between 110 
                                                                        and 120;

or by wrapping the statment like this:

select *
  from (select a.pkey
              ,datediff(day, max(a.date), coalesce(max(b.date), getdate())) as diff 
          from table1      a
          left join table2 b on(b.ref# = a.pkey)
         group 
            by a.pkey
       )
 where diff between 110 and 120;

Answer 2

SELECT t1.PKEY, CASE WHEN o.DATE IS NULL 
                     THEN DATEDIFF(DAY, t1.DATE, GETDATE()) 
                     ELSE DATEDIFF(DAY, t1.DATE, o.DATE) END AS DIFF
FROM dbo.table1 t1 OUTER APPLY (
                                SELECT MAX(t2.Date) AS Date
                                FROM dbo.table2 t2     
                                WHERE t1.PKEY = t2.REF# 
                                ) o

Demo on SQLFiddle

OR without APPLY() operator

SELECT t1.PKEY, (SELECT CASE WHEN MAX(o.Date) IS NULL 
                             THEN DATEDIFF(DAY, t1.Date, GETDATE()) 
                             ELSE DATEDIFF(DAY, t1.Date, MAX(o.Date)) END AS DIFF
                 FROM dbo.test27 o
                 WHERE t1.PKEY = o.REF#
                 ) AS DIFF                               
FROM dbo.test26 t1  

query with your criteria

SELECT *
FROM (
      SELECT t1.PKEY, (SELECT CASE WHEN MAX(o.Date) IS NULL 
                              THEN DATEDIFF(DAY, t1.Date, GETDATE()) 
                              ELSE DATEDIFF(DAY, t1.Date, MAX(o.Date)) END AS DIFF
                       FROM dbo.test27 o
                       WHERE t1.PKEY = o.REF#
                       ) AS DIFF                               
      FROM dbo.test26 t1
      ) s
WHERE s.DIFF BETWEEN 110 AND 120   

Answer 3

WITH recorddates
AS
(
    SELECT  PKEY, REFNo, DATE,
            ROW_NUMBER() OVER (PARTITION BY RefNO
                                ORDER BY DATE DESC) rn
    FROM    table2
)
SELECT  a.PKEY, 
        datediff(day,a.DATE,b.date) 
FROM    table1 a
        INNER JOIN recorddates b
            ON a.PKey = b.RefNo
WHERE   b.rn = 1