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.
3条答案
按热度按时间ac1kyiln1#
This can be done using
group by
andhaving
clause, the condition is getting Claims having at least one Drug-X and none of H99 Code :Demo here
py49o6xq2#
It's probably more efficient to do this using window functions, rather than a self-join.
eivgtgni3#
Here's a solution that uses
exists
: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.