Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Oct 2004
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts

    SQL Server 2005: Using MAX in Query

    I have two tables: one with person information and the other with a person's status changes/updates. (A person's status can go from Prospective to Current to Completed.)


    Code:
    Person Table:
    UserName  FullName 
    jsmith    Joe Smith
    bjones    Bob Jones
    cblack    Carol Black
    
    Status Table:
    UserName  Status        DateChanged
    jsmith    Prospective   1/1/2006
    bjones    Prospective   1/2/2006
    jsmith    Current       3/3/2006
    cblack    Prospective   3/3/2006
    bjones    Current       4/4/2006
    bjones    Completed     5/5/2006
    From the Status Table it's obvious that "bjones" is Completed, "cblack" is Prospective, and "jsmith" is Current. But I'm not sure how to grab this info from these tables in a select. I'm thinking I might need the MAX(DateChanged) in there somewhere, but I have no clue how to use it.

    Eventually my goal is to say "show me all the names of the people who are Current".

    Any ideas? Thanks in advance, I hate SQL!

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    select fullname
    from person
    inner join status
    on person.username = status.username
    where status = 'Current'

    unless, the person can downgrade, because then a record with status current will be returned while the person is not current anymore
    Is that the case?
    I am the luckiest man in the world

  • #3
    New Coder
    Join Date
    Oct 2004
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you for your response!

    They can't downgrade their status, but they can upgrade. I think the query that you posted would return both Joe Smith and Bob Jones. However, Bob's most recent status is Completed so he should not show up in the Current list anymore. A query that would show me all the names of people who are current should only show Joe Smith.

    I need for the query to only count a person's most recent status (a.k.a. the one with the most recent date in the DateChanged column of the Status table.
    Last edited by melissa820; 10-31-2007 at 09:33 PM. Reason: type-o

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    Sorry, i did not notice that current was not the last status. But here is a query that works:
    Code:
    SELECT p.fullname, s.status, s.datechanged
    FROM person p
    INNER JOIN status s
    ON s.username = p.username
    WHERE (
    s.datechanged = (
         SELECT     MAX(datechanged)
         FROM status s2
         WHERE p.username = s2.username))
    AND (s.status = 'Current')
    I am the luckiest man in the world

  • Users who have thanked Roelf for this post:

    melissa820 (11-01-2007)

  • #5
    New Coder
    Join Date
    Oct 2004
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    This is perfect, thank you so much for your help!!


  •  

    Posting Permissions

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