Results 1 to 7 of 7

Thread: Need assistance with a select query.

  1. #1
    Join Date
    Feb 2006
    Location
    Tuscaloosa, AL
    Posts
    4

    Need assistance with a select query.

    I am new to SQL and aftering reading my SQL For Dummies book, I still am unsure how to accomplish this task.

    The table I need to query contains resident census information. There are multiple rows for each resident. I need to determine if the resident is still active, so I only need to read the last row for each resident to make this determination.

    What method can I use to read only the last row for the resident. Here is what I tried previously, but it returns multiple rows for each resident.

    select a.firm_id_code as FacAddOnNum,
    f.shortname as FacName,
    left(a.resident_code,6) as ResidentCode,
    (r.res_first_name + ' ' + r.res_last_name) as ResidentName,
    a.LastDate,
    a.cens_trans_type

    from (select firm_id_code,
    resident_code,
    max(dt_cens_trans) as LastDate,
    cens_trans_type
    from arrescensus
    where firm_id_code = @FacId
    group by firm_id_code, resident_code, dt_cens_trans, cens_trans_type) as a
    join corp_info.dbo.facilityinfo as f on (a.firm_id_code = f.addonnum)
    join arresidents as r on ((r.firm_id_code = a.firm_id_code) and (r.resident_code = a.resident_code))


    Any help with this is greatly appreciated!

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Do you have sample data?

  3. #3
    Join Date
    Feb 2006
    Location
    Tuscaloosa, AL
    Posts
    4
    I have working on using a scrollable cursor select, thought I still don't have it quite working yet? Do you think I heading in the right direction, or is there a simpler solution?

    Here is some sample data.
    FacAddOnNum FacName ResidentCode ResidentName LastDate cens_trans_type
    075 West Melbourne 000086 LOTTIE AUSTIEN 1999-07-19 00:00:00.000 S
    075 West Melbourne 000086 LOTTIE AUSTIEN 2000-05-28 00:00:00.000 E
    075 West Melbourne 000086 LOTTIE AUSTIEN 1999-11-03 00:00:00.000 T
    075 West Melbourne 000086 LOTTIE AUSTIEN 1999-09-02 00:00:00.000 S
    075 West Melbourne 000086 LOTTIE AUSTIEN 1999-10-31 00:00:00.000 S
    075 West Melbourne 000086 LOTTIE AUSTIEN 1999-07-27 00:00:00.000 D
    075 West Melbourne 000086 LOTTIE AUSTIEN 1999-08-03 00:00:00.000 A
    075 West Melbourne 000086 LOTTIE AUSTIEN 2000-02-29 00:00:00.000 T
    075 West Melbourne 000086 LOTTIE AUSTIEN 1999-11-11 00:00:00.000 S
    075 West Melbourne 000086 LOTTIE AUSTIEN 1998-01-23 00:00:00.000 A
    075 West Melbourne 000086 LOTTIE AUSTIEN 1999-08-17 00:00:00.000 S
    075 West Melbourne 000086 LOTTIE AUSTIEN 1999-10-02 00:00:00.000 S
    075 West Melbourne 000129 EVELYN MORIN 2001-10-01 00:00:00.000 S
    075 West Melbourne 000129 EVELYN MORIN 1995-11-28 00:00:00.000 A
    075 West Melbourne 000129 EVELYN MORIN 2003-01-01 00:00:00.000 E

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    What's the result you like to get?

  5. #5
    Join Date
    Feb 2006
    Location
    Tuscaloosa, AL
    Posts
    4
    One row for Lottie Austien, for 199-10-02, and one for row for Evelyn Morin, 2003-01-01.

    I only need the last row for each resident, in order to determine their current status, which is determined by the cens_trans_type column.

  6. #6
    Join Date
    Dec 2004
    Posts
    502
    You didn't provide all the data from all the tables, so I can't give you a complete answer. However, this is what you would do without the joins:

    SELECT *
    FROM arrescensus
    JOIN
    (SELECT ResidentCode, MAX(LastDate) AS MaxLastDate
    FROM arrescensus
    GROUP BY ResidentCode) AS A
    ON arrescensus.ResidentCode = A.ResidentCode AND arrescensus.LastDate = A.MaxLastDate

  7. #7
    Join Date
    Feb 2006
    Location
    Tuscaloosa, AL
    Posts
    4
    I have scored a TD.

    I reworked the original query I started with and achieved the desired results. Thanks rmiao and nosepicker for your input.

    LC

Posting Permissions

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