Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014

    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)));

  2. #2
    Join Date
    Jul 2015
    up cho thớt nhé

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.