GrpID GrpName PrvID PrvName Spc
1234 Med Associates (blank) (blank) Mixed
1234 Med Associates 6875 Bill Ham Cardiology
1234 Med Associates 3274 Sarah Jones Cardiology
8975 Pulm Associates (blank) (blank) Mixed
5781 Babies Inc. (blank) (blank) Pediatrics
6321 ABC Therapy (blank) (blank) Mixed
6321 ABC Therapy 4925 Tate Silverman Physical Therapy
6321 ABC Therapy 3274 Sarah Jones Occupational Therapy
The column Spc contains either the Specialty of the Group (Grp) if the record has no Provider (Prv), or contains the Specialty of the Provider (Prv).
I am needing the query to do two things
1. Return the Groups (Grp) with a Specialty (Spc) equal to Mixed that have no Providers (Prv)
And
select GrpId from #mytable where spc like 'mixed' group by GrpId having max(PrvId) =0
2. Return the Groups (Grp) with a Specialty (Spc) equal to Mixed but where the Providers (Prv) only have one other type of Specialty (Spc).
select GrpId from
(select GrpId,varr=(case when PrvID IS null then 0 when PrvID = '' then 0 else 1 end) from #mytable where spc like 'mixed')a group by GrpId
having sum(varr)=1