-- Tables

-- Alters

ALTER TABLE AKUZ.T_PALLIATIVE_REGISTRY ADD "patientgroup" integer NULL;

UPDATE AKUZ.T_PALLIATIVE_REGISTRY r
SET PatientGroup = coalesce(x.PatientGroup, (select cr.PatientGroup from AKUZ.T_PALLIATIVE_CARE_RECORD cr where cr.PallieativeRegistry = r.PalliativeRegistryID order by cr.Date desc limit 1), 1)
from (
    select ru.palliativeregistryid, rm.PatientGroup
    from akuz.t_palliative_registry ru
	    left join AKUZ.T_PALLIATIVE_REGISTRY_MANIPULATION rm on ru.Patient = rm.Patient
    where not exists(select * from AKUZ.T_PALLIATIVE_REGISTRY_MANIPULATION rmm
                            where rmm.Patient = rm.Patient
                              and rmm.PalliativeRegistryManipulationID != rm.PalliativeRegistryManipulationID
                              and rm.Date > rmm.Date)) x
where r.palliativeregistryid = x.palliativeregistryid;

ALTER TABLE AKUZ.T_PALLIATIVE_REGISTRY ALTER COLUMN "patientgroup" SET NOT NULL;

ALTER TABLE AKUZ.T_PALLIATIVE_CARE_RECORD DROP COLUMN "patientgroup";