update the answer of a column on group by-CodePudding

I am working on a query where

PatientNo.	VisitNo.	DischargeCode	DischargeStatus
1	2	6	Survived
1	3	6	Survived
1	4	6	Survived
1	5	7	Died

There is a patient, who visited 4 times in the hospital (visit numbers are 2, 3, 4, 5). If the patient died in the journey of the visits then there should be a fifth column as PatientFinalStatus as Died.

If the patient survived in the journey, then status should be survived.

So in the case shown above, the fifth column should be PatientFinalStatus and the value should be Died in all 4 rows.

It should be grouped by patientno, and then for each visit number check what is the status. Wherever it finds died, it should update the whole column to Died, otherwise Survived.

Could anyone please suggest a way to do this?

Thanks

CodePudding user response：

You may try with conditional count window function as the follwing:

SELECT *,
       CASE WHEN COUNT(CASE WHEN DischargeStatus='Died' THEN 1 END) OVER
                      (PARTITION BY PatientNo) > 0 
        THEN 'Died'
        ELSE 'Survived'
       END AS PatientFinalStatus
FROM table_name
ORDER BY PatientNo, VisitNo

If you have only two possibilities for 'DischargeStatus' (Died or Survived), you can simply use MIN window function as the following:

SELECT *,
       MIN(DischargeStatus) OVER (PARTITION BY PatientNo) PatientFinalStatus
FROM table_name
ORDER BY PatientNo, VisitNo

See a demo.