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
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:
FROM Prospect pr
(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.
FROM Prospect AS pr
JOIN ( SELECT ProspectID
, MAX(ActivityDate) AS last_activity
BY ProspectID) AS m
ON m.ProspectID = pr.ProspectID
JOIN Activity AS a
ON a.ProspectID = m.ProspectID
AND a.ActivityDate = m.last_activity
Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!
Ofcourse! Two equal statements in the Join
a.ProspectIDY = m.ProspectIDY
AND a.DateCompleted = m.last_activity
Aslong as two activities don't happen on the same date and time this will work perfect.