CODEWITHSUNDEEP
sqlsql-servert-sql
user3833239
user3833239

Reputation: 49

Get rows with joined column matching a specific pattern

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.

Upvotes: 0

Views: 54

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415705

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

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

Upvotes: 2

Related Questions