ALTER TABLE [AKUZ].[T_POST_RELATIONSHIP] ALTER COLUMN [Post] int NOT NULL
GO
ALTER TABLE [AKUZ].[T_POST_RELATIONSHIP] ALTER COLUMN [AmbulanceCardRecord] uniqueidentifier NOT NULL
GO

ALTER TABLE [AKUZ].[T_POST_RELATIONSHIP] ADD [EhrRusMdp365] int NULL
GO

DECLARE @nurse INT;
DECLARE @assistent INT;

SELECT TOP 1 @nurse = ehr.EhrRusID
	FROM EHR.T_EHR_RUS_MDP365 ehr
WHERE ehr.IdEHR = 125;

SELECT TOP 1 @assistent = ehr.EhrRusID
	FROM EHR.T_EHR_RUS_MDP365 ehr
WHERE ehr.IdEHR = 246;

UPDATE pr
	SET EhrRusMdp365 = @nurse
	FROM AKUZ.T_POST_RELATIONSHIP pr
INNER JOIN AKUZ.T_MEDICAL_RECORD mr
	ON mr.MedicalRecordID = pr.AmbulanceCardRecord
INNER JOIN VCLib.T_ENTITIES e
	ON e.EntityID = mr.type_
WHERE e.EntName IN ('MEDICAL_EXAMINATION', 
                    'DOCTOR_WARD_EXAMINATION', 
                    'MEDICAL_EXAMINATION_MAIN', 
                    'MEDICAL_EXAMINATION_RE', 
                    'REANIMATION_EXAMINATION');

UPDATE pr
	SET EhrRusMdp365 = @assistent
	FROM AKUZ.T_POST_RELATIONSHIP pr
INNER JOIN AKUZ.T_MEDICAL_RECORD mr
	ON mr.MedicalRecordID = pr.AmbulanceCardRecord
INNER JOIN VCLib.T_ENTITIES e
	ON e.EntityID = mr.type_
WHERE e.EntName IN ('DIAGNOSTICS_RESULTS', 
                    'ANGIO_DIAGNOSTICS', 
                    'ARMIS_DIAGNOSTICS', 
                    'CARDIO_DIAGNOSTICS', 
                    'DIAGNOSTICS_RESULTS_HISTOLOGICAL', 
                    'ECHOCARDIOGRAPHY_PROTOCOL', 
                    'HEARING_DIAGNOSTICS', 
                    'HEMODIALYSIS_RECORD', 
                    'HITECH_DIAGNOSTICS', 
                    'HOLTER_DIAGNOSTICS', 
                    'INTOXICATION_RESEARCH_RESULT', 
                    'MMG_DIAGNOSTICS', 
                    'PREGNANT_DIAGNOSTICS', 
                    'PRG_DIAGNOSTICS', 
                    'X_RAY_DIAGNOSTICS');   


ALTER TABLE [AKUZ].[T_POST_RELATIONSHIP] ALTER COLUMN [EhrRusMdp365] int NOT NULL
GO

ALTER TABLE [AKUZ].[T_POST_RELATIONSHIP] ADD CONSTRAINT [FK_T_EHR_RUS_MDP365_T_POST_RELATIONSHIP_EhrRusMdp365]
FOREIGN KEY([EhrRusMdp365]) REFERENCES [EHR].[T_EHR_RUS_MDP365] ([EhrRusID])
GO




