How to apply logic to pull certain rows based on results in the Where Clause

I have a query that, in a perfect world, would pull, for example...

If Bill has 3 rows of data:

I'm trying to apply logic where if a patient has an Active status I'd just want that row to display: Active

But if Sara has 3 rows:

I'd want all 3 Pendings to show. Only if the patient has an Active will just the Active show

Right now my results are pulling (for the Bill example above)

Instead of

Here is an example query:

select distinct
,[RX Number]
from Table1

where Status = case when d.Status = 'ACTIVE' then Status
when d.Status <> 'ACTIVE' then Status end

--- *** Test Data which you should have provided. ***
	Patient varchar(20) NOT NULL
	,PorA varchar(20) NOT NULL
VALUES ('Bill','Pending')
-- *** End Test Data ***

-- One option
WITH PatientActive
	SELECT Patient, PorA
		,MAX(CASE WHEN PorA = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY Patient) AS IsActive
	FROM #t
SELECT Patient, PorA
FROM PatientActive
WHERE IsActive = 0
	OR PorA = 'Active';
1 Like

That worked! Thanks! I appreciate you.