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
>Solution :
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