I have a table MemBER_Claims with fowwing columns:
MemberID Claim Date P Code Cost T Code Cycle 4036 7/30/2010 P 22 A 1 4036 8/9/2010 D 4 B 1 4036 9/24/2010 U 24 B 1 4036 4/5/2011 P 27 A 2 4036 4/26/2011 F 32 B 2 4036 8/29/2011 F 45 A 3 4036 9/7/2011 U 15 B 3
I have to get rid of row with date 9/24/2010. That is, If there are two consecutive T-code 'B' followed by 'A', then it should pick only the row with T-code 'B' with min claimdate.
Any help is appreciated.
;with cte as (select *, row_number(0 over (partition by MemberId, [T Code], [Cycle] order by [Claim Date]) as Rn from dbo.Member_Claims) delete from cte where Rn > 1 -- delete all extra rows per the same MemberId and [T Code]
--(e.g. if we have 2 or more rows with code B all rows will be deleted expect for the row with the earliest date).
For every expert, there is an equal and opposite expert. - Becker's Law