I have two tables as follows:
Table A : contacts
| Contact_Primary_Num | Displayed_Name |
|---|---|
| 1 | Smith, Joe |
| 2 | Doe, Donna |
| 3 | Jerry, Tom |
| 4 | Crocker, Betty |
Table B : contact_notes
| Contact_Note_Primary_Num | Contact_Num | Contact_Note_Date | Contact_Follow_Up_Date |
|---|---|---|---|
| 1 | 2 | 2024-06-19 | 2024-06-24 |
| 2 | 1 | 2024-03-05 | NULL |
| 3 | 1 | 2024-02-29 | NULL |
| 4 | 4 | NULL | NULL |
| 5 | 3 | 2024-06-20 | 2024-06-26 |
| 6 | 3 | 2024-06-19 | 2024-06-27 |
| 7 | 3 | 2024-06-20 | 2024-06-27 |
How do I get a solution that groups by ContactPrimaryNum, filters by latest ConactNoteDate, then filters by latest ContactFollowUpDate for each ContactPrimaryNum? Such as :
| Contact_Primary_Num | Displayed_Name | ContactNotePrimaryNum | Contact_Num | Contact_Note_Date | Contact_Follow_Up_Date |
|---|---|---|---|---|---|
| 4 | Crocker, Betty | 4 | 4 | NULL | NULL |
| 1 | Smith, Joe | 2 | 1 | 2024-03-05 | NULL |
| 2 | Doe, Donna | 1 | 2 | 2024-06-19 | 2024-06-24 |
| 3 | Jerry, Tom | 7 | 3 | 2024-06-20 | 2024-06-27 |
I’ve tried creating a Temp Table where I use a LEFT OUTER JOIN to combine the two tables and then filter using the ContactFollowUpDate from there, but it leaves out the “NULL” entries. Example:
CREATE TABLE #ContactListA
(
ContactPrimaryNum int,
DisplayedName nvarchar(max),
ContactNotePrimaryNum int,
ContactNum int,
ContactNoteDate nvarchar(max),
ContactFollowUpDate nvarchar(max)
)
INSERT INTO #ContactListA
(
ContactPrimaryNum,
DisplayedName,
ContactNotePrimaryNum,
ContactNum,
ContactNoteDate,
ContactFollowUpDate
)
SELECT
a.Contact_Primary_Num,
a.Displayed_Name,
b.Contact_Note_Primary_Num,
b.Contact_Num,
b.Contact_Note_Date,
b.Contact_Follow_Up_Date
FROM contacts a
LEFT OUTER JOIN contact_notes b ON b.Contact_Num = a.Contact_Primary_Num
WHERE
(b.Conact_Note_Date = (SELECT max(b1.Contact_Note_Date) FROM contact_notes b1 WHERE b1.Contact_Num = b.Contact_Num)
SELECT
a.ContactPrimaryNum,
a.DisplayedName,
a.ContactNotePrimaryNum,
a.ContactNum,
a.ContactNoteDate,
c.ContactFollowUpDate
FROM (
SELECT ContactPrimaryNum, max(ContactFollowUpDate) as ContactFollowUpDate
FROM #ContactListA
GROUP BY ContactPrimaryNum
) c
INNER JOIN #ContactListA a
ON a.ContactPrimaryNum = c.ContactPrimaryNum AND a.ContactFollowUpDate = c.ContactFollowUpDate
ORDER BY ContactFollowUpDate ASC
3
Using a series of groupings and joins:
select c.*, n2.contact_note_primary_num, t1.dt, t1.dt1 from
(select n1.contact_num, t.dt, max(n1.contact_follow_up_date) dt1 from
(select n.contact_num, max(n.contact_note_date) dt
from contact_notes n group by n.contact_num) t
join contact_notes n1 on n1.contact_num = t.contact_num
and t.dt = n1.contact_note_date
group by n1.contact_num, t.dt) t1
right join contacts c on c.contact_primary_num = t1.contact_num
left join contact_notes n2 on n2.contact_note_date = t1.dt and (n2.contact_follow_up_date is null or n2.contact_follow_up_date = t1.dt1)
See fiddle