-
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!
-
-
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
-
What's the result you like to get?
-
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.
-
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
-
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
-
Forum Rules
|
|