CREATE TABLE FOR_UPDATE_1234_4567_890(
	 ID UNIQUEIDENTIFIER
	,DATEREC_NEW DATETIME
	,DATEREC_OLD DATETIME
);

--TRUNCATE TABLE FOR_UPDATE_1234_4567_890

INSERT INTO FOR_UPDATE_1234_4567_890(ID, DATEREC_OLD)
SELECT ACR.MedicalRecordID, COALESCE(MR.RecordDate, eb.CreationDateTime, eb.EventDate, '20240101')
FROM AKUZ.T_AMBULANCE_CARD_RECORD ACR
	LEFT JOIN AKUZ.T_MEDICAL_RECORD MR ON MR.MEDICALRECORDID = ACR.MEDICALRECORDID
	left join AKUZ.T_EVENT_BASE eb on eb.EventID = acr.AmbulanceEvent
WHERE ACR.POSTINGDATE IS NULL;
GO

CREATE CLUSTERED INDEX IX_FOR_UPDATE_1234_4567_890 ON FOR_UPDATE_1234_4567_890 (ID);

GO

UPDATE Main
	SET Main.DATEREC_NEW = ME.ExaminationDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_MEDICAL_EXAMINATION ME ON ME.MEDICALRECORDID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = pr.RouteDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_PRESCRIPTION_RECORD pr ON pr.MedicalRecordID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = aso.OperationDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_AMBULANCE_SURGICAL_OPERATION aso on aso.MedicalRecordID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = r.RecipeDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_RECIPE r on r.MedicalRecordID = Main.ID

UPDATE Main
SET Main.DATEREC_NEW = eb.EventDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_ANKETA a on A.MedicalRecordID = Main.ID
	JOIN AKUZ.T_EVENT_BASE eb on eb.EventID = a.ExaminationBase

UPDATE Main
	SET Main.DATEREC_NEW = d.RegistrationDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_DIAGNOSIS d on d.MedicalRecordID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = mc.MedcertOutDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_MEDICAL_CERTIFICATE mc on mc.MedicalRecordID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = toh.DateOver
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_TAKE_OVER_HOSPITALISATION toh on toh.MedicalRecordID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = eb.EventDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_PREPARE_DATA_HOSPITALISATION pdh on pdh.MedicalRecordID = Main.ID
	JOIN AKUZ.T_EVENT_BASE eb on eb.EventID = pdh.EmergencyEvent

UPDATE Main
	SET Main.DATEREC_NEW = r.ReferralDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_REFERRAL r on r.MedicalRecordID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = cd.DateCancel
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_CANCEL_DIAGNOSTICS cd on cd.MedicalRecordID = Main.ID

UPDATE Main
SET Main.DATEREC_NEW = ISNULL(acc.PostingDate, mr.RecordDate)
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_ACCOMPANYING_DISEASES ad on ad.MedicalRecordID = Main.ID
	JOIN AKUZ.T_MEDICAL_RECORD mr on mr.MedicalRecordID = Main.ID
	JOIN AKUZ.T_AMBULANCE_CARD_RECORD acc on acc.MedicalRecordID = ad.OutcomeOfInspection

UPDATE Main
	SET Main.DATEREC_NEW = i.InspectionDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_INSPECTIONTOSIFFILIS i on i.MedicalRecordID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = pmp.DateBegin
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_PATIENT_MONITORING_PROGRAM pmp on pmp.MedicalRecordID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = cr.DateCancel
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_CANCEL_ROUTE cr on cr.MedicalRecordID = Main.ID


UPDATE Main
	SET Main.DATEREC_NEW = pdr.DDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_PRG_DIAGNOSTICS_RE pdr on pdr.MedicalRecordID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = orct.ExaminationDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_ONK_REGISTRY_CARD_TALON orct on orct.MedicalRecordID = Main.ID

UPDATE Main
SET Main.DATEREC_NEW = ocrb.ResearchDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_ONK_CYTOLOGY_RESULT_BASE ocrb on ocrb.MedicalRecordID = Main.ID

UPDATE Main
	SET Main.DATEREC_NEW = onk.GetTenderloinDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_ONK_ANATOMICAL_EXAMINATION_PROTOCOL_RESULT onkk on onkk.MedicalRecordID = Main.ID
	JOIN AKUZ.T_ONK_ANATOMICAL_EXAMINATION_PROTOCOL onk on onk.AddParamsBaseID = onkk.OnkAnatomicalExaminationProtocol

UPDATE Main
	SET Main.DATEREC_NEW = cct.RegisterDate
FROM FOR_UPDATE_1234_4567_890 Main
	JOIN AKUZ.T_CHILDBIRTH_CERTIFICATE_TALON cct on cct.MedicalRecordID = Main.ID

CREATE TABLE TMP_FOR_DELETE_CYCLE(
	 ID UNIQUEIDENTIFIER
	,DATEREC_NEW DATETIME
	,DATEREC_OLD DATETIME
);

GO

CREATE INDEX IX_TMP_FOR_DELETE_CYCLE ON TMP_FOR_DELETE_CYCLE (ID);

GO

DECLARE @C VARCHAR(50) = ''

WHILE ((SELECT COUNT(*) FROM FOR_UPDATE_1234_4567_890) > 0)
BEGIN
	
	-- Удаляем строки из первой таблицы и вставляем во вторую
	DELETE TOP (10000) Main
	OUTPUT DELETED.ID, DELETED.DATEREC_NEW, DELETED.DATEREC_OLD INTO TMP_FOR_DELETE_CYCLE (ID, DATEREC_NEW, DATEREC_OLD)
	FROM FOR_UPDATE_1234_4567_890 Main

	UPDATE ACR
		SET ACR.POSTINGDATE = ISnull(TFDC.DATEREC_NEW, TFDC.DATEREC_OLD)
	FROM AKUZ.T_AMBULANCE_CARD_RECORD ACR
		JOIN TMP_FOR_DELETE_CYCLE TFDC ON TFDC.ID = ACR.MedicalRecordID

	-- Очищение 2й времянки для последующей вставки следующих строк
	TRUNCATE TABLE TMP_FOR_DELETE_CYCLE
	
END;

GO

DROP TABLE TMP_FOR_DELETE_CYCLE;

DROP TABLE FOR_UPDATE_1234_4567_890;

GO

if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_AMBULANCE_CARD_RECORD', 'U') and i.name = 'IX_AMBULANCE_CARD_RECORD_AmbulanceEvent')
	drop index IX_AMBULANCE_CARD_RECORD_AmbulanceEvent on AKUZ.T_AMBULANCE_CARD_RECORD;
go
if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_AMBULANCE_CARD_RECORD', 'U') and i.name = 'IX_T_AMBULANCE_CARD_RECORD_Movement')
	drop index IX_T_AMBULANCE_CARD_RECORD_Movement on AKUZ.T_AMBULANCE_CARD_RECORD;
go

if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_AMBULANCE_CARD_RECORD', 'U') and i.name = 'IX_T_AMBULANCE_CARD_RECORD_PostingDate')
	drop index IX_T_AMBULANCE_CARD_RECORD_PostingDate on AKUZ.T_AMBULANCE_CARD_RECORD;
go

if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_AMBULANCE_CARD_RECORD', 'U') and i.name = 'IX_T_AMBULANCE_CARD_RECORD_AmbulanceCard')
	drop index IX_T_AMBULANCE_CARD_RECORD_AmbulanceCard on AKUZ.T_AMBULANCE_CARD_RECORD;
go

ALTER TABLE AKUZ.T_AMBULANCE_CARD_RECORD ALTER COLUMN PostingDate datetime NOT NULL
GO


--CREATE INDEX IX_T_AMBULANCE_CARD_RECORD_AmbulanceCard ON AKUZ.T_AMBULANCE_CARD_RECORD ( AmbulanceCard, PostingDate ) INCLUDE ( AmbulanceEvent, Movement) WITH (FILLFACTOR=90, SORT_IN_TEMPDB= ON);
--GO

--CREATE INDEX IX_T_AMBULANCE_CARD_RECORD_Movement ON AKUZ.T_AMBULANCE_CARD_RECORD(Movement, PostingDate) INCLUDE(AmbulanceCard, AmbulanceEvent) WITH (FILLFACTOR=95);
--go

--CREATE INDEX IX_AMBULANCE_CARD_RECORD_AmbulanceEvent ON AKUZ.T_AMBULANCE_CARD_RECORD ( AmbulanceEvent, PostingDate ) INCLUDE ( AmbulanceCard, Movement) WITH (FILLFACTOR=90, SORT_IN_TEMPDB= ON);
--go

--CREATE INDEX IX_T_AMBULANCE_CARD_RECORD_PostingDate_Includes ON AKUZ.T_AMBULANCE_CARD_RECORD (PostingDate)  INCLUDE (AmbulanceCard, AmbulanceEvent, PatientBase) WITH (FILLFACTOR=90, SORT_IN_TEMPDB= ON);
--go