ALTER TABLE VMP.T_VMP_EXECUTION_RESULT_STAGE ADD CurrentStatus int NOT NULL CONSTRAINT df_CurrentStatus DEFAULT 1 WITH VALUES
GO
ALTER TABLE VMP.T_VMP_EXECUTION_RESULT_STAGE DROP CONSTRAINT df_CurrentStatus
GO
update stage
set stage.CurrentStatus = stat.Status
from VMP.T_VMP_EXECUTION_RESULT_STAGE_STATUS stat
join VMP.T_VMP_STAGE_STATUS ss on ss.VmpStageStatusID = stat.VmpStageStatusID
join VMP.T_VMP_EXECUTION_RESULT_STAGE stage on stage.VmpEventStageID = ss.VmpEventStage
where not exists(select * 
					from VMP.T_VMP_STAGE_STATUS ss1 
					where VmpStageStatusID != stat.VmpStageStatusID
							and ss1.VmpEventStage = ss.VmpEventStage
							and ss1.Date >= ss.Date)

ALTER TABLE VMP.T_POLYCLINIC_VMP_STAGE ADD CurrentStatus int NOT NULL CONSTRAINT df_CurrentStatus DEFAULT 1 WITH VALUES
GO
ALTER TABLE VMP.T_POLYCLINIC_VMP_STAGE DROP CONSTRAINT df_CurrentStatus
GO
update stage
set stage.CurrentStatus = stat.Status
from VMP.T_POLYCLINIC_VMP_STAGE_STATUS stat
join VMP.T_VMP_STAGE_STATUS ss on ss.VmpStageStatusID = stat.VmpStageStatusID
join VMP.T_POLYCLINIC_VMP_STAGE stage on stage.VmpEventStageID = ss.VmpEventStage
where not exists(select * 
					from VMP.T_VMP_STAGE_STATUS ss1 
					where VmpStageStatusID != stat.VmpStageStatusID
							and ss1.VmpEventStage = ss.VmpEventStage
							and ss1.Date >= ss.Date)

ALTER TABLE VMP.T_MEDICAL_CARE_CONFIRMATION_STAGE ADD CurrentStatus int NOT NULL CONSTRAINT df_CurrentStatus DEFAULT 1 WITH VALUES
GO
ALTER TABLE VMP.T_MEDICAL_CARE_CONFIRMATION_STAGE DROP CONSTRAINT df_CurrentStatus
GO
update stage
set stage.CurrentStatus = stat.Status
from VMP.T_MEDICAL_CARE_CONFIRMATION_STAGE_STATUS stat
join VMP.T_VMP_STAGE_STATUS ss on ss.VmpStageStatusID = stat.VmpStageStatusID
join VMP.T_MEDICAL_CARE_CONFIRMATION_STAGE stage on stage.VmpEventStageID = ss.VmpEventStage
where not exists(select * 
					from VMP.T_VMP_STAGE_STATUS ss1 
					where VmpStageStatusID != stat.VmpStageStatusID
							and ss1.VmpEventStage = ss.VmpEventStage
							and ss1.Date >= ss.Date)

ALTER TABLE VMP.T_CHOICE_MEDICAL_CARE_PROVIDER_STAGE ADD CurrentStatus int NOT NULL CONSTRAINT df_CurrentStatus DEFAULT 1 WITH VALUES
GO
ALTER TABLE VMP.T_CHOICE_MEDICAL_CARE_PROVIDER_STAGE DROP CONSTRAINT df_CurrentStatus
GO
update stage
set stage.CurrentStatus = stat.Status
from VMP.T_CHOICE_MEDICAL_CARE_PROVIDER_STAGE_STATUS stat
join VMP.T_VMP_STAGE_STATUS ss on ss.VmpStageStatusID = stat.VmpStageStatusID
join VMP.T_CHOICE_MEDICAL_CARE_PROVIDER_STAGE stage on stage.VmpEventStageID = ss.VmpEventStage
where not exists(select * 
					from VMP.T_VMP_STAGE_STATUS ss1 
					where VmpStageStatusID != stat.VmpStageStatusID
							and ss1.VmpEventStage = ss.VmpEventStage
							and ss1.Date >= ss.Date)


ALTER TABLE VMP.T_VMP_EVENT ADD CurrentStatus int NOT NULL CONSTRAINT df_CurrentStatus DEFAULT 1 WITH VALUES
GO
ALTER TABLE VMP.T_VMP_EVENT DROP CONSTRAINT df_CurrentStatus
GO

update e
set e.CurrentStatus = case when pol.VmpStageStatusID is not null then case when pol.Status = 1 then 1
                                                                           when pol.Status = 2 then 8
                                                                           when pol.Status = 3 then 2 end
                           when mccss.VmpStageStatusID is not null then case when mccss.Status = 1 then 2
                                                                             when mccss.Status = 2 then 8
                                                                             when mccss.Status = 3 then 4
                                                                             when mccss.Status = 4 then 3 end
                           when cmcp.VmpStageStatusID is not null then case when cmcp.Status = 1 or cmcp.Status = 2 or cmcp.Status = 6 then 4
                                                                            when cmcp.Status = 3 then 5
                                                                            when cmcp.Status = 5 then 8 end
                           when execS.VmpStageStatusID is not null then case when execS.Status = 1 or execS.Status = 2 then 6
                                                                             when execS.Status = 3 then 7
                                                                             when execS.Status = 4 then 8 end
                            end
--select * 
from VMP.T_VMP_STAGE_STATUS ss    
join VMP.T_VMP_EVENT_STAGE stage on stage.VmpEventStageID = ss.VmpEventStage
join VMP.T_VMP_EVENT e on e.VmpEventID = stage.VmpEvent
left join VMP.T_POLYCLINIC_VMP_STAGE_STATUS pol on pol.VmpStageStatusID = ss.VmpStageStatusID
left join VMP.T_MEDICAL_CARE_CONFIRMATION_STAGE_STATUS mccss on mccss.VmpStageStatusID = ss.VmpStageStatusID
left join VMP.T_CHOICE_MEDICAL_CARE_PROVIDER_STAGE_STATUS cmcp on cmcp.VmpStageStatusID = ss.VmpStageStatusID
left join VMP.T_VMP_EXECUTION_RESULT_STAGE_STATUS execS on execS.VmpStageStatusID = ss.VmpStageStatusID
where not exists(select * 
                        from VMP.T_VMP_STAGE_STATUS ssS    
                        join VMP.T_VMP_EVENT_STAGE stageS on stageS.VmpEventStageID = sss.VmpEventStage
                        join VMP.T_VMP_EVENT eS on eS.VmpEventID = stageS.VmpEvent 
                        where ssS.VmpStageStatusID <> ss.VmpStageStatusID and eS.VmpEventID = e.VmpEventID
                        and ssS.Date >= ss.Date)