0

I am working with medical data and have a list of claim numbers. Those claim numbers have multiple revenue codes associated with each claim.

I am attempting to eliminate any claims that have a REVENUE_CD of 450.

The result however, still includes claims with that revenue code. It appears to be returning claims that have the 450 along with other codes. I need it to look at all the Revenue codes and eliminate claims that have 450 as any of the multiple codes.

SELECT claim_num
FROM claims c
WHERE revenue_cd NOT IN ('0450')

RESULTS:

ICN_NUM REVENUE_CD
1   1335    0352
2   1335    0300
3   1335    0252
4   1335    0306
5   1335    0324
6   1335    0272
7   1335    0762
8   1335    0305
9   1335    0450
10  1335    0637

Any help is greatly appreciated.

FutbolFan
  • 13,235
  • 3
  • 23
  • 35
Kris A
  • 11
  • 1
  • 2
  • By "multiple codes", what do you mean? Please post a sample of the `claims` table showing what the `revenue_cd` column actually looks like. I have a hunch it may be something like a comma-separated list which wouldn't work properly with `IN ()`. – Michael Berkowski Aug 06 '15 at 18:20
  • What I mean is that each claim has a number of the rev codes associated with it. There can be up to 15 rev codes per claim. I'm not able to post the table as it is quite large and I'm actually joining the claims table to another table that contains the revenue codes. – Kris A Aug 06 '15 at 18:25
  • 1
    Can you edit above to include that info? It isn't clear how it is to be formatted. You don't need to post the whole table, just a sample of as many rows as needed to illustrate the problem, and only the relevant columns. It just isn't clear whether you have multiple rows per claim_num, or multiple revenue_cd values stuffed into one row rather than normalized. – Michael Berkowski Aug 06 '15 at 18:29

1 Answers1

3

that isn't the correct approach for that.

One way would be

SELECT claim_num
FROM   claims c
GROUP  BY claim_num
HAVING COUNT(CASE
               WHEN revenue_cd = '0450' THEN 0
               ELSE 1
             END) = 0

Or another

SELECT claim_num
FROM   claims
EXCEPT 
SELECT claim_num
FROM   claims
WHERE revenue_cd = '0450'

Or you could use a correlated subquery with NOT IN or NOT EXISTS also

SELECT c1.claim_num
FROM   claims c1
WHERE  c1.claim_num NOT IN (SELECT c2.claim_num
                            FROM   claims c2
                            WHERE  c2.revenue_cd = '0450'
                                   AND c2.claim_num IS NOT NULL)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845