Grab record satisfying multiple case statements

Question

I am trying to grab a record that meets multiple conditions. See below.

DATA:

PERSON_ID	DOCTOR	DATE_OF_SERVICE
1234	Dr. Smith	2022-01-01
1234		2022-01-01
1234	Dr. Jane	2022-03-01
1234		2022-06-01

DESIRED OUTPUT:

ID	DOCTOR	DATE_OF_SERVICE
1234	Dr. Smith	2022-01-01
1234	Dr. Jane	2022-03-01
1234		2022-06-01

Basically, if a person_id has the same date_of_service but one record has a doctor populated and the other doesn't, take the record where the doctor is not null.

BUT - if there is only one record where there is no doctor listed, then it is okay to keep.

Is this doable? Any help would be greatly helpful

Answer 1

Use NOT EXISTS:

SELECT t1.*
FROM tablename t1
WHERE t1.doctor IS NOT NULL
   OR NOT EXISTS (
            SELECT *
            FROM tablename t2
            WHERE t2.person_id = t1.person_id 
              AND t2.date_of_service = t1.date_of_service 
              AND t2.doctor IS NOT NULL
          );

See the demo.