Hi guys,
I tried to search but it seems there's a problem with the search engine at the moment. I'm suffering a bit of a brain freeze and hopefully someone can help me out with this...
I have two tables I'm focusing on, one contains plans and the second contains contact made with the holders of those plans. I want to pull out all plans, and for each plan - look up the date of the latest contact (
including plans where no contact has been made, i.e. no records exist for that plan in the contact table).
Can anyone suggest the best way of doing this? I've found the following snippet online which is close, but it excludes plans where no contact records exist. Thanks in advance for any advice you can give.
-
SELECT p.plan_id, c.[date] AS latest_contact
-
-
FROM plan p
-
LEFT JOIN plan_contact c ON c.plan_id = p.plan_id
-
-
WHERE p.contact_id IN (
-
SELECT TOP 1 contact_id
-
FROM plan_contact c
-
WHERE c.plan_id = p.plan_id
-
ORDER BY [date] DESC
-
)
-
-
ORDER BY plan_id ASC
-