Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Location
    Nashville, TN
    Posts
    3

    Unanswered: max date with specific criteria from same table

    I am having a problem selecting records whose status code = 'N' only if it is the record with the lastest date. In other words if I have multiple records for the same person that looks like this:

    ID STATUS_CODE ACTIVITY_DATE
    1234 I 14-AUG-2009
    1234 N 1-AUG-2009

    I wouldn't want this person on my list because the latest date doesn't have a status of N. Instead I am getting everyone with an N regardless of whether or not it's the lastest record. I have tried several different scenarios with no luck. This is what I have that isn't working. I have this embedded in with other joins and selection criteria, but this is the part that doesn't seem to be working. I am still fairly new to SQL, so don't be too hard on me.

    select * from (select tzrstsf_pidm,tzrstsf_activity_date, tzrstsf_status_code, tzrstsf_term
    from tzrstsf,
    (select max(z.tzrstsf_activity_date)as maxdate from tzrstsf z group by z.tzrstsf_pidm) maxresults
    where tzrstsf_term = '200980' and tzrstsf_activity_date = maxresults.maxdate)
    where tzrstsf_status_code = 'N';

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Max date per id:
    Code:
    SELECT id
         , Max(activity_date)
    FROM   your_table
    GROUP
        BY id
    Join back to self to get status_code
    Code:
    SELECT a.id
         , a.status_code
         , a.activity_date
    FROM   your_table As a
     INNER
      JOIN (
            SELECT id
                 , Max(activity_date) As b.max_activity_date 
            FROM   your_table
            GROUP
                BY id
           ) As b
        ON b.id = a.id
       AND b.max_activity_date = a.activity_date
    Final where clause:
    Code:
    SELECT a.id
         , a.status_code
         , a.activity_date
    FROM   your_table As a
     INNER
      JOIN (
            SELECT id
                 , Max(activity_date) As b.max_activity_date 
            FROM   your_table
            GROUP
                BY id
           ) As b
        ON b.id = a.id
       AND b.max_activity_date = a.activity_date
    WHERE  a.status_code = 'N'
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2009
    Location
    Nashville, TN
    Posts
    3
    This is what I tried and it gave me an error message sql command not properly ended on the second line.

    select a.tzrstsf_pidm, a.tzrstsf_status_code, a.tzrstsf_activity_date
    from tzrstsf as a
    inner join (select tzrstsf_pidm,
    max(tzrstsf_activity_date) as b.max_activity_date
    from tzrstsf
    group by tzrstsf_pidm) as b
    on b.tzrstsf_pidm = a.tzrstsf_pidm
    and b.max_activity_date = a.tzrstsf_activity_date
    where a.tzrstsf_status_code = 'N'

Posting Permissions

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