SQL Statement - Nested Query?

Databases
1

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!

2 
SELECT pr.ProspectID
     , pr.Name
     , a.ActivityDate
     , a.ActivityCode 
  FROM Prospect AS pr
LEFT OUTER
  JOIN ( SELECT ProspectID
              , MAX(ActivityDate) AS last_activity
           FROM Activity
         GROUP
             BY ProspectID) AS m
    ON m.ProspectID = pr.ProspectID
LEFT OUTER
  JOIN Activity AS a
    ON a.ProspectID = m.ProspectID
   AND a.ActivityDate = m.last_activity

:slight_smile:

3

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.

Thank you!