May 27, 2010 at 10:24 am
i have a table with all the followups we do for students.
For each participant, we have n number of followups and gather information like their employer,current wage ctc.
now i want to extract the last followup date information for each participant.
ex:
Participant_id followup_date employer hourly_wage
649 2009-10-24 xxxx $9.00
649 2008-11-22 yyyyy $5.00
723 2010-11-01 zzzz $4.00
723 2009-01-01 mmm $7.00
Now i want the o/p as
Participant_id followup_date Employer hourly_wage
649 2009-10-24 xxxxx $9.00
723 2010-11-01 zzzz $4.00
I tried using MAX and TOp but those are not giving the o/p that i need
Any help appreciated
Thanks
May 27, 2010 at 10:31 am
Since this is your first post, I'll fill you in on a bit of forum best practices. It helps us a bunch if you post example DDL, data, and any SQL that you've already tried. Please read through the link in my signature line for furture posts.
Something like this should work:
DECLARE @Table TABLE (Participant_ID int, followup_date datetime, employer varchar(25), hourly_wage money)
INSERT INTO @Table
SELECT 1, '2010-01-01', 'Test', 7.00 UNION ALL
SELECT 1, '2010-02-01', 'Test', 7.00 UNION ALL
SELECT 1, '2010-03-01', 'Test', 10.00 UNION ALL
SELECT 1, '2010-04-01', 'Test', 11.00 UNION ALL
SELECT 2, '2010-01-01', 'Test2', 12.00 UNION ALL
SELECT 2, '2010-03-01', 'Test2', 12.00 UNION ALL
SELECT 2, '2010-04-01', 'Test2', 9.00
SELECT t1.*
FROM@Table t1
INNER JOIN (
SELECT Participant_ID,
MAX(Followup_Date) as MostRecent
FROM@Table
GROUP BY Participant_ID
) t2 ON t1.Participant_ID = t2.Participant_ID AND t1.followup_date = t2.MostRecent
May 27, 2010 at 10:53 am
Thanks for ur help.It worked.
Next time when i post i follow the process you mentioned.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply