SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot keith5885's Avatar
    Join Date
    Jun 2006
    Location
    at my computer
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question SQL Statement - Nested Query?

    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:


    Code SQL:
    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!
    Thanks Sitepoint!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot keith5885's Avatar
    Join Date
    Jun 2006
    Location
    at my computer
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    Thanks Sitepoint!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •