Get rows with joined column matching a specific pattern

Question

OK - I have a table with a claim ID and a mix of procedureCodes - What I need to do is to isolate the claimIDs where only procedure codes exist that start with an 8 - so in the below, I need to be able to grab only those records with claimIDs where claimID LIKE '8%' codes exist - no other procedureCodes, etc. - so only 2 and 3 claimID from below

    claimID procedureCode
    1       85025
    1       97110
    2       85025
    2       80102
    3       87112
    3       81020

I want to exclude a claimID if there are any records for a claim that start with a different digit.

@JoelCoehoorn, it would be excluded because ID 1 also has a procedure code that starts with 9 — Tab Alleman, Mar 31 '15 at 16:54
Want to edit the subject line too while you're at it? : ) It's misleading at best. — Tab Alleman, Mar 31 '15 at 16:58

score 2 · Answer 1 · answered Mar 31 '15 at 16:57

SELECT DISTINCT yt1.ClaimID
    FROM YourTable yt1
    WHERE yt1.procedureCode LIKE '8%'
        AND NOT EXISTS (SELECT 1
                            FROM YourTable yt2
                            WHERE yt2.ClaimID = yt1.ClaimID
                                AND yt2.procedureCode NOT LIKE '8%');

score 1 · Answer 2 · edited May 23 '17 at 11:50

We do this in three steps. The first step is to find records that fail the "No other leading digit" requirement. The second step is to find records the meet the "must start with an 8" requirement. The third step is to match the two sets together with an exclusion join, such that we take the set from step 2 and exclude the set from step 1:

We also need to know the datatype of the procedureCode column. Knowing a little bit about CPT codes, I'd expect char(5) or similar is the most appropriate choice, depending on how you handle modifiers. If that is how you stored it, then this should work:

With BadClaims As ( --Step 1
  SELECT distinct ClaimID
  FROM [Table]
  WHERE procedureCode NOT LIKE '8%'
), GoodClaims AS ( --Step 2
  SELECT distinct ClaimID
  FROM [Table]
  WHERE procedureCode LIKE '8%'
)
SELECT g.ClaimID --Step 3
FROM GoodClaims g
LEFT JOIN BadClaims b on b.ClaimID = g.ClaimID
WHERE b.ClaimID IS NULL

However, you may have stored it as integer/numeric field. If that's the case, this should work:

With BadClaims As ( --Step 1
  SELECT distinct ClaimID
  FROM [Table]
  WHERE procedureCode < 80000 or procedureCode >= 90000
), GoodClaims AS ( --Step 2
  SELECT distinct ClaimID
  FROM [Table]
  WHERE procedureCode LIKE >= 80000 and procedureCode < 90000
)
SELECT g.ClaimID --Step 3
FROM GoodClaims g
LEFT JOIN BadClaims b on b.ClaimID = g.ClaimID
WHERE b.ClaimID IS NULL

That's the long form, to explain what's going on here. You can also simplify these steps into a single self-join:

SELECT distinct t1.claimID 
FROM [Table] t1
LEFT JOIN [Table] t2 ON t2.claimID = t1.ClaimID and t2.procecureCode NOT LIKE '8%'
WHERE t1.procedureCode LIKE '8%' and t2.claimID IS NULL

he doesn't want claimID 1, only the ones that ONLY have procedureCodes starting with '8'. ClaimID 1 has a procedureCode starting with '9' — mxix, Mar 31 '15 at 16:55
Might want to edit this line in your second code sample: WHERE ClaimID LIKE >= 80000 and ClaimID < 90000. And I think you want to be looking at procedureCode instead of ClaimID. Also if procedureCode is a numeric, it could simply be CAST as a varchar and used with a LIKE operator. — Tab Alleman, Mar 31 '15 at 17:12

Get rows with joined column matching a specific pattern

2 Answers2