How Can I Get The Max Date (per Primarykey) And Get Its Difference With The One From Another Table?

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

Solution 1:

WITH recorddates
AS
(
    SELECT  PKEY, REFNo, DATE,
            ROW_NUMBER() OVER (PARTITIONBY RefNO
                                ORDERBYDATEDESC) rn
    FROM    table2
)
SELECT  a.PKEY, 
        datediff(day,a.DATE,b.date) 
FROM    table1 a
        INNERJOIN recorddates b
            ON a.PKey = b.RefNo
WHERE   b.rn =1

Solution 2:

SELECT t1.PKEY, CASEWHEN o.DATEIS NULL 
                     THEN DATEDIFF(DAY, t1.DATE, GETDATE()) 
                     ELSE DATEDIFF(DAY, t1.DATE, o.DATE) ENDAS DIFF
FROM dbo.table1 t1 OUTER APPLY (
                                SELECT MAX(t2.Date) ASDateFROM dbo.table2 t2     
                                WHERE t1.PKEY = t2.REF# 
                                ) o

Demo on SQLFiddle

OR without APPLY() operator

SELECT t1.PKEY, (SELECTCASEWHEN MAX(o.Date) IS NULL 
                             THEN DATEDIFF(DAY, t1.Date, GETDATE()) 
                             ELSE DATEDIFF(DAY, t1.Date, MAX(o.Date)) ENDAS 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, (SELECTCASEWHEN MAX(o.Date) IS NULL 
                              THEN DATEDIFF(DAY, t1.Date, GETDATE()) 
                              ELSE DATEDIFF(DAY, t1.Date, MAX(o.Date)) ENDAS DIFF
                       FROM dbo.test27 o
                       WHERE t1.PKEY = o.REF#
                       ) AS DIFF                               
      FROM dbo.test26 t1
      ) s
WHERE s.DIFF BETWEEN 110AND120

Solution 3:

Something like this?

select a.pkey
      ,datediff(day, max(a.date), coalesce(max(b.date), getdate())) as diff 
  from table1      aleft join table2 bon(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      aleft join table2 bon(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
          leftjoin table2 b on(b.ref# = a.pkey)
         groupby a.pkey
       )
 where diff between110and120;