filter out expired records

Question

Post reply

filter out expired records

rightfield

SSCrazy

Points: 2418
More actions
July 22, 2010 at 11:14 am

#227117

I have a table that has these columns, clientnumber, code, plan, and eff_date, and more. Some records have the same clientnumber, code, and plan as another record but the eff_date is different. I would like to retrieve the record with the most recent eff_date).

client code plan eff_date additionalcol_1 additionalcol_2 more_cols 5678 300 abc 04/01/2007 anything1 anything 1 more1 5678 300 abc 09/01/2009 anything2 anything2 more2 5678 300 xyz 09/01/2009 anything3 anything3 more3 5678 301 nop 06/01/2008 anything4 anything4 more4 1234 300 abc 09/01/2009 anything5 anything5 more 5

I would like to retrieve records 2, 3, 4 and 5 but not 1 since it is the same clientnumber, code, and plan as record 2 but it has an older eff_date.

Any help on what type of join could do this would be appreciated.

Warm regards,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

just use a subquery to determine the records you want to keep:



SELECT	t.*

FROM	@temp t

		INNER JOIN (

			SELECT	clientnumber, code, plan, MAX(eff_date) as eff_date

			FROM	@temp

			GROUP BY clientnumber, code, plan

		) x ON t.clientnumber = x.clientnumber

			AND t.code = x.code

			AND t.plan = x.plan

			AND t.eff_date = x.eff_date