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

1

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

If Bill has 3 rows of data:
•Pending
•Pending
•Active

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:
•Pending
•Pending
•Pending

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)
•Pending
•Pending
•Active

Instead of
•Active

Here is an example query:

select distinct
MRN
,[RX Number]
,[Status]
from Table1

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

2 
--- *** Test Data which you should have provided. ***
CREATE TABLE #t
(
	Patient varchar(20) NOT NULL
	,PorA varchar(20) NOT NULL
);
INSERT INTO #t
VALUES ('Bill','Pending')
	,('Bill','Pending')
	,('Bill','Active')
	,('Sara','Pending')
	,('Sara','Pending')
	,('Sara','Pending');
-- *** End Test Data ***

-- One option
WITH PatientActive
AS
(
	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
4

That worked! Thanks! I appreciate you.