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

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

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

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

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;

</details>



# 答案3
**得分**: 0

以下是使用`exists`的解决方案：

```sql
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;

将其分解，CTE（Common Table Expression） badClaims 寻找具有 Drug-X 的项目，并且在同一索赔中没有任何项目的 IDC10 = H99。一旦找到这样的索赔，我们返回该索赔的所有项目。

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;