SQL Help- Querying with Join/Where on 2 variables, one of which is derived
I have a table, claims, that has dup values for orig_claim_num. To get rid of the dups, I need to take the max of the final 2 variables in claim_number- which I'm calling suffix. This suffix is NOT a variable in the original table, but a derived field from claim_num. I've created two queries. the first one is working fine- the second one isn't. The last two lines of the second query are two different attempts I've tried to get this to work. I'd prefer to use a where statement rather than a join statement as that seems to process quicker, but whatever works in the end is fine with me.
Can anyone help me???
Create table Clean_Claims_1 as (Select orig_claim_num, Max(Right(claim_number,2)) as Suffix from claims where product_id in ('MC11','MC12','MC13','MC14') and adj_status in ('PAID') and right(claim_number,1) Not in ('R','r') group by 1);
Create table Clean_Claims as (Select * From claims a
Join clean_claims_1 b on (a.orig_claim_num = b.orig_claim_num and right(a.claim_number,2)=b.suffix));
Where (a.orig_claim_num, right(a.claim_number,2) in (Select b.orig_claim_num, b.suffix from Clean_Claims_1 b)));