ALTER TABLE [AKUZ].[T_PALLIATIVE_REGISTRY] ADD [PatientGroup] int NULL
GO

UPDATE r
SET r.[PatientGroup] = isnull(isnull(
	rm.[PatientGroup], 
	(select top 1 cr.[PatientGroup] 
		from AKUZ.T_PALLIATIVE_CARE_RECORD cr 
		where cr.PallieativeRegistry = r.PalliativeRegistryID 
		order by cr.[Date] desc)), 1) -- select *
from [AKUZ].[T_PALLIATIVE_REGISTRY] r
	left join AKUZ.T_PALLIATIVE_REGISTRY_MANIPULATION rm on r.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]
)
GO

ALTER TABLE [AKUZ].[T_PALLIATIVE_REGISTRY] ALTER COLUMN [PatientGroup] int NOT NULL
GO

ALTER TABLE [AKUZ].[T_PALLIATIVE_CARE_RECORD] DROP COLUMN [PatientGroup]
GO