SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (
  Claim_ID INT,
  Person_ID INT,
  Service VARCHAR(20),
  ICD10_Code VARCHAR(20)
);

insert into mytable values
(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','');
8 rows affected

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 


Claim_ID


321


Warning: Null value is eliminated by an aggregate or other SET operation.


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


Claim_ID
Person_ID
Service
ICD10_Code


321
556
Injection
H88

321
556
Drug-X


321
556
Exam


321
556
Diagnosis



Warning: Null value is eliminated by an aggregate or other SET operation.