Get Most recent date from 2 tables and 2 persons

Question

I have tables:

   Person
-------------
ID | name

           Notes
---------------------------
targetID | Content | Date

      CallHistory
--------------------------
CallerID | CalleeID | Date

Now say I have a Person and a Spouse (which is on the same Person table). They have corresponding notes and callhistory.

What I want is to Select the most recent date from either the spouse or person's most recent date (call or note)

Ive tried:

SELECT top 1 Date, ID from (select TargetID as ID, Date from notes
                        union
                       SELECT CalleeID as ID, Date from Callhistory)
WHERE ID in (person.ID, spouse.ID)

but without luck.

EDIT: This select is inside a select statement:

select p.*, SELECT top 1 Date, ID from (select TargetID as ID, Date from notes
                            union
                           SELECT CalleeID as ID, Date from Callhistory)
    WHERE ID in (person.ID, spouse.ID) as RecentContactDate
From Person person
LEFT JOIN PersonRelationship pr on person.ID = pr.ID AND pr.Type = 3 -- spouse
LEFT JOIN Person spouse on pr.RelatedID = spouse.ID
......

Im getting Ambigous column name Date error.

any ideas?

How do you know they are spouses? I don't see that relationship in your tables. — Jen R, Apr 19 '17 at 14:05
whether they are spouses or not is irrelevant. I say, what if i have to IDs..? — xGeo, Apr 19 '17 at 14:13
You said "either the spouse or person's most recent date" - so you need to have a way to determine their relationship. — Jen R, Apr 19 '17 at 14:17
please read: Now ***SAY*** I have a Person and a Spouse..... — xGeo, Apr 19 '17 at 14:20
@beanfrog I am getting syntax error. I will update my question to show how. — xGeo, Apr 19 '17 at 14:22
Some sample data will go a very long way and will enable us to assist you. — Mark Kram, Apr 19 '17 at 14:54

score 0 · Accepted Answer · edited May 23 '17 at 12:03

@Sven's answer on this post has helped me solve my problem: SQL MAX of multiple columns?

What I've done is join the two tables from the two persons into a single row (need to get the MAX dates for each person's tables - CallHistory and Notes). Then get the MAX out of that four columns. So here's the fix:

select p.*,
(SELECT Max(v) 
   FROM (VALUES (pNote.Date), (pCh.Date), (sNote.Date),(sCh.Date)) AS value(v)) as [MaxDate]
    WHERE ID in (person.ID, spouse.ID) as RecentContactDate
From Person person
LEFT JOIN PersonRelationship pr on person.ID = pr.ID AND pr.Type = 3 -- spouse
LEFT JOIN Person spouse on pr.RelatedID = spouse.ID
LEFT JOIN (SELECT ID, MAX(Date) from Notes) pNote on pNote.targetID = person.ID
LEFT JOIN (SELECT ID, MAX(Date) from CallHistory) pCh on person.ID = ch.CalleeID
LEFT JOIN (SELECT ID, MAX(Date) from Notes) sNote on spouse.ID = pNote.targetID
LEFT JOIN (SELECT ID, MAX(Date) from CallHistory) sCh on spouse.ID = ch.CalleeID

I hope this will help anyone in the future.

Get Most recent date from 2 tables and 2 persons

1 Answers1