Hello I need help because this is driving me crazy. Any help would work. I can’t believe I can’t figure this out.
Lets say I have a table called ACTIVITY with
ProspectID, ActivityCode, and ActivityDate as Columns
with data like
24 | 35 | 3/4/2010
24 | 45 | 2/6/2009
24 | 24 | 2/2/2009
25 | 25 | 5/5/2009
25 | 35 | 5/9/2009
25 | 45 | 5/8/2010
And then a table of PROSPECT
ProspectID, Name
with date like
24 | Jeff
25 | John
What query will get me a list of all my Prospects and their most recent (MAX ActivityDate) Activity Code
So I would want to see
24 | Jeff | 35
25 | John | 45
I can get the most recent date for each person by doing this:
SELECT
MAX(a.ActivityDate),
pr.Name
pr.ProspectID
FROM Prospect pr
LEFT JOIN
(Select ProspectID, ActivityDate,ActivityCode from Activity) a
ON a.ProspectID = pr.ProspectID
GROUP BY pr.Name, pr.ProspectID
But I can not get the activitycode related with that Date.
Any Ideas? I will keep trying but I feel like others this may come easy to them. I am having the hardest time.
Thanks Everyone!