SQL Server SQL - How to find a set of records that does NOT include a certain column value

2g32fytz  于 2023-08-02  发布在  其他
关注(0)|答案(3)|浏览(117)

I am working with healthcare claims data and I need assistance proving out a hypothesis.

I want to find all claims of a particular drug that were not billed with the appropriate diagnosis code.

So say I have Drug-X that can only be billed if there is a diagnosis code of H99 (in the below Claim 123 is valid because it has the right code), I want to find claims that were billed with Drug-X but did NOT have a diagnosis code of H99 at all.

Also, the ICD10 Code can be attached to any row for a service (or all services) of that claim

In the below example: I'd like to be able to return just the records where the Claimd ID is 321
| Claim ID | Person ID | Service | ICD10 Code |
| ------------ | ------------ | ------------ | ------------ |
| 123 | 555 | Injection | H99 |
| 123 | 555 | Drug-X | |
| 123 | 555 | Exam | |
| 123 | 555 | Diagnosis | |
| 321 | 556 | Injection | H88 |
| 321 | 556 | Drug-X | |
| 321 | 556 | Exam | |
| 321 | 556 | Diagnosis | |

I have tried using some where in (select distinct Claim ID) and Exists functions but they don't seem to work.

ac1kyiln

ac1kyiln1#

This can be done using group by and having clause, the condition is getting Claims having at least one Drug-X and none of H99 Code :

select t.*
from mytable t
inner join (
  select Claim_ID
  from mytable
  group by Claim_ID
  having count(case when Service = 'Drug-X' then 1 end) > 0
         and count(case when ICD10_Code = 'H99' then 1 end) = 0 
) as s on s.Claim_ID = t.Claim_ID

Demo here

py49o6xq

py49o6xq2#

It's probably more efficient to do this using window functions, rather than a self-join.

select t.*
from (
    select *,
      count_DrugX = count(case when Service = 'Drug-X' then 1 end) over (partition by Claim_ID),
      count_H99   = count(case when ICD10_Code = 'H99' then 1 end) over (partition by Claim_ID)
    from mytable t
) t
where count_DrugX > 0
  and count_H99 = 0;
eivgtgni

eivgtgni3#

Here's a solution that uses exists :

with yourData as (
    select *
    from (values
        ('123', '555', 'Injection', 'H99'),
        ('123', '555', 'Drug-X', NULL),
        ('123', '555', 'Exam',NULL),
        ('123', '555', 'Diagnosis', NULL),
        ('321', '556', 'Injection', 'H88'),
        ('321', '556', 'Drug-X', NULL),
        ('321', '556', 'Exam',  NULL),
        ('321', '556', 'Diagnosis',  NULL)
    ) as x(ClaimID, PersonID, [Service], [IDC10])
), badClaims as (
    select ClaimID
    from yourData
    where [Service] = 'Drug-X'
        and not exists (
            select *
            from yourData as t
            where yourData.ClaimID = t.ClaimID
                and IDC10 = 'H99'
        )
)
select *
from yourData
join badClaims
    on badClaims.ClaimID = yourData.ClaimID;

Breaking it down, the cte badClaims looks for line items that have Drug-X and no line item in the same claim has IDC10 = H99. Once such claims are found, we return all line items for that claim.

相关问题