IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_FR_LoadBeneficiariesWithUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_FR_LoadBeneficiariesWithUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2018-02-02
-- Description: Загрузка федеральных льготников в RDS 
-- =============================================
CREATE PROCEDURE [dbo].[sp_FR_LoadBeneficiariesWithUpdate]
	@File uniqueidentifier = null
	,@CurrentDateTime datetime
	,@Full bit = 0
AS
BEGIN
	IF (EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[idb].[FED_REG1]') AND type in (N'U'))
		and EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[idb].[FED_REG2]') AND type in (N'U')))
	begin
	
		DECLARE @ver int, @datee char(10)--, @StartVer int 
		--set @StartVer = 0	
		set @datee='99991231'
		set @CurrentDateTime = case when DatePart(Day,@CurrentDateTime) <  10 then cast((cast(datepart(Year, @CurrentDateTime) as varchar(4)) + RIGHT(REPLICATE('0',2)+ cast(datepart(Month, @CurrentDateTime) as varchar(2)), 2) + '01') as date) 
									when DatePart(Day,@CurrentDateTime) >= 10 and DAtePart(Day,@CurrentDateTime) < 20  then cast((cast(datepart(Year, @CurrentDateTime) as varchar(4)) + RIGHT(REPLICATE('0',2)+ cast(datepart(Month, @CurrentDateTime) as varchar(2)), 2) + '10') as date)
									when DatePart(Day,@CurrentDateTime) >= 20 and ((DAtePart(Day,@CurrentDateTime) < 31 and  DAtePart(Month,@CurrentDateTime) <> 12) or (DAtePart(Day,@CurrentDateTime) < 28 and  DAtePart(Month,@CurrentDateTime) = 12))  then cast((cast(datepart(Year, @CurrentDateTime) as varchar(4)) + RIGHT(REPLICATE('0',2)+ cast(datepart(Month, @CurrentDateTime) as varchar(2)), 2) + '20') as date)
									when DatePart(Day,@CurrentDateTime) >= 28 and DAtePart(Month,@CurrentDateTime) = 12 then cast((cast(datepart(Year, @CurrentDateTime)+1 as varchar(4)) +'01' + '01') as date)
									end
		SET NOCOUNT ON;

		--update fr2
		--	set fr2.DATE_DOC = case 
		--						when fr2.DATE_DOC = ''
		--							then fr2.DATE_BL		
		--						else fr2.DATE_DOC
		--					end
		--	   ,fr2.DATE_EL = case 
		--	  					when fr2.DATE_EL = '99999999'
		--							then case 
		--									when cast( r1.DATE_RSE as datetime) = cast( '19000101' as datetime) 
		--										then @datee 
		--									else r1.DATE_RSE end
		--						when cast( r1.DATE_RSE as datetime) <> cast( '19000101' as datetime) and cast( fr2.DATE_EL as datetime) > cast( r1.DATE_RSE as datetime)
		--							then r1.DATE_RSE
		--						else fr2.DATE_EL
		--					end			
		--from idb.FED_REG2 fr2
		--join idb.FED_REG1 r1  on r1.ss = fr2.ss 

		update fr2
			set fr2.DATE_DOC =	case 
									when fr2.DATE_DOC = '' then @datee			
									else fr2.DATE_DOC
								end
			   ,fr2.DATE_EL =	case 
			  						when fr2.DATE_EL = '99999999' then @datee
									else fr2.DATE_EL
								end			
		from idb.FED_REG2 fr2

		create index ci on idb.FED_REG1 (CodeIzm) WHERE CodeIzm <> '00'
		create index ss on idb.FED_REG1 (SS)
		create index ss1 on idb.FED_REG2 (SS)

		--if (exists (select Snils from RDS.T_FR_RECORD group by Snils HAVING count(*) > 1))
		--begin
		--raiserror ('В справочнике присутствуют 2 одинаковых записи по СНИЛС!', 18, 1)
		--end
	
		select @ver = max([Version]) 
		from [RDS].T_FEDERAL_REGISTER_LOAD	
		if @ver is null
		BEGIN
			SET @ver = 1
			delete from [RDS].[T_FEDERAL_REGISTER_LOAD]
			delete from [RDS].T_FR_RECORD
			delete from [RDS].T_FR_DOCUMENT
			delete from [RDS].T_FR_RECORD
		END
		ELSE
		BEGIN
			SET @ver = @ver + 1
		END

		--IF(@StartVer <> 0 and @StartVer is not null)
		--begin
		--	delete from [RDS].[T_FEDERAL_REGISTER_LOAD]
		--	delete from [RDS].T_FR_RECORD
		--	delete from [RDS].T_FR_DOCUMENT
		--	delete from [RDS].T_FR_RECORD
		--	SET @ver = @StartVer
		--end 

		-- делаем insert в [RDS].T_FEDERAL_REGISTER_LOAD		
		INSERT INTO [RDS].[T_FEDERAL_REGISTER_LOAD]
			(
			 [FederalRegisterLoadID]
			,[Users]
			,[Version]
			,[UpdateDate]
			,[File]
			)
		select
			NEWID(),
			NULL,
			@ver,
			@CurrentDateTime,
			@File	

		update fr 
		set 
			fr.LastName				= rtrim(ltrim( r1.fam)), 
			fr.FirstName			= rtrim(ltrim( r1.im)), 
			fr.FatherName			= rtrim(ltrim( r1.ot)), 
			fr.Birthday				= cast(dr as datetime), 
			fr.Sex					= CASE 
										WHEN (UPPER(LTRIM(RTRIM(W))) = 'Ж')THEN 1 
										WHEN (UPPER(LTRIM(RTRIM(W))) = 'М')THEN 0 
										ELSE null 
									  END,
			fr.RegionCode			= rtrim(ltrim( r1.RegionCode)), 
			fr.RajonCode			=  rtrim(ltrim( r1.RajonCode)), 
			fr.GR					= rtrim(ltrim( r1.GR)), 
			fr.FormaPr				= rtrim(ltrim( r1.FormaPr)), 
			fr.DocCode				= rtrim(ltrim( r1.DocCode)), 
			fr.DocType				= rtrim(ltrim( r1.DocType)), 
			fr.DocSeries			= rtrim(ltrim( r1.DocSeries)), 
			fr.DocNumber			= rtrim(ltrim( r1.DocNumber)), 
			fr.DocDate				= cast( r1.DocDate as datetime), 
			fr.DocOrg				= rtrim(ltrim( r1.DocOrg)), 
			fr.AddrLegal			= rtrim(ltrim( r1.AddrLegal)), 
			fr.AddrReal				= rtrim(ltrim( r1.AddrReal)), 
			fr.Location				= rtrim(ltrim( r1.Location)),
			fr.RecNumber			= rtrim(ltrim( r1.RecNumber)), 
			fr.prizn1				= rtrim(ltrim( r1.prizn1)), 
			fr.prizn2				= rtrim(ltrim( r1.prizn2)), 
			fr.prizn3				= rtrim(ltrim( r1.prizn3)), 
			fr.C_KAT1				= rtrim(ltrim( r1.C_KAT1)), 
			fr.C_KAT2				= rtrim(ltrim( r1.C_KAT2)),
			fr.DB_EDV				= cast( r1.DB_EDV as datetime), 
			fr.DE_EDV				= case when cast( r1.DE_EDV as datetime) = cast( '19000101' as datetime) then null else cast( r1.DE_EDV as datetime) end,
			fr.DATE_RSB				= cast( r1.DATE_RSB as datetime), 
			fr.DATE_RSE				= case when cast( r1.DATE_RSE as datetime) = cast( '19000101' as datetime) then null else cast( r1.DATE_RSE as datetime) end,
			fr.CodeIzm				= rtrim(ltrim( r1.CodeIzm)),
			fr.prizn1Next			= rtrim(ltrim( r1.prizn1Next))
			from [RDS].T_FR_PATIENT fr
			join idb.FED_REG1 r1  on r1.ss = fr.SNILS 
		--where r1.CodeIzm <> '00'
		
		insert into [RDS].T_FR_PATIENT 
			(
			 FrPatientID
			,Snils
			,LastName
			,FirstName
			,FatherName
			,Birthday
			,Sex
			,RegionCode
			,RajonCode
			,GR
			,FormaPr
			,DocCode
			,DocType
			,DocSeries
			,DocNumber
			,DocDate
			,DocOrg
			,AddrLegal
			,AddrReal
			,[Location]
			,RecNumber
			,prizn1
			,prizn2
			,prizn3
			,C_KAT1
			,C_KAT2
			,DB_EDV
			,DE_EDV
			,DATE_RSB
			,DATE_RSE
			,CodeIzm
			,prizn1Next
			)
		select NEWID()
			,ss
			,rtrim(ltrim(fam))
			,rtrim(ltrim(im))
			,rtrim(ltrim(ot))
			,cast(dr as datetime)
			,CASE 
				WHEN (UPPER(LTRIM(RTRIM(W))) = 'Ж') THEN 1 
				WHEN (UPPER(LTRIM(RTRIM(W))) = 'М')THEN 0 
				ELSE null 
			 END
			,rtrim(ltrim(RegionCode))
			,rtrim(ltrim(RajonCode))
			,rtrim(ltrim(GR))
			,rtrim(ltrim(FormaPr))
			,rtrim(ltrim(DocCode))
			,rtrim(ltrim(DocType))
			,rtrim(ltrim(DocSeries))
			,rtrim(ltrim(DocNumber))
			,cast(DocDate as datetime)
			,rtrim(ltrim(DocOrg))
			,rtrim(ltrim(AddrLegal))
			,rtrim(ltrim(AddrReal))
			,rtrim(ltrim(Location))
			,rtrim(ltrim(RecNumber))
			,rtrim(ltrim(prizn1))
			,rtrim(ltrim(prizn2))
			,rtrim(ltrim(prizn3))
			,rtrim(ltrim(C_KAT1))
			,rtrim(ltrim(C_KAT2))
			,cast(DB_EDV as datetime)
			,case when cast( r1.DE_EDV as datetime) = cast( '19000101' as datetime) then null else cast( r1.DE_EDV as datetime) end
			,cast(DATE_RSB as datetime)
			,case when cast( r1.DATE_RSE as datetime) = cast( '19000101' as datetime) then null else cast( r1.DATE_RSE as datetime) end
			,rtrim(ltrim(CodeIzm))
			,rtrim(ltrim(prizn1Next))
		from idb.FED_REG1 r1
		where not exists (select * from [RDS].T_FR_PATIENT fr where fr.Snils = r1.ss)
		--and r1.CodeIzm <> '00'
		
		update frd
		set frd.[DATE_DOC]		= r.DATE_DOC,
			frd.[N_ORGAN]		= r.[N_ORGAN],
			frd.[DATE_BL]		= r.[DATE_BL],
			frd.[DATE_EL]		= r.[DATE_EL],
			frd.[PR]			= r.[PR],
			frd.[RESERVE]		= r.[RESERVE]
		from [RDS].T_FR_DOCUMENT frd
			join idb.FED_REG2 r  on r.ss = frd.Snils
			join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = 1
			join [RDS].T_BENEFITS b on b.BenefitCode = r.c_kat and b.BenefitViews = e.EnumID
		where ltrim(rtrim(r.sn_dl)) = frd.Series and ltrim(rtrim(r.nom_dl)) = frd.Number and b.BenefitsID = frd.Benefits

		insert into [RDS].T_FR_DOCUMENT
			(
			 [FrDocumentID]
			,[Name]
			,[Series]
			,[Number]
			,[Benefits]
			,[Snils]
			,[DATE_DOC]
			,[N_ORGAN]
			,[DATE_BL]
			,[DATE_EL]
			,[PR]
			,[RESERVE]
			)
		select NEWID()
			,ltrim(rtrim(r2.name_d))
			,ltrim(rtrim(r2.sn_dl))
			,ltrim(rtrim(r2.nom_dl))
			,b.BenefitsID
			,r2.ss
			,cast(DATE_DOC as datetime)
			,rtrim(ltrim(N_ORGAN))
			,cast(DATE_BL as datetime)
			,cast(DATE_EL as datetime)
			,rtrim(ltrim(PR))
			,rtrim(ltrim(r2.RESERVE))	
		from idb.FED_REG2 r2
			join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = 1
			join [RDS].T_BENEFITS b on b.BenefitCode = r2.c_kat and b.BenefitViews = e.EnumID
			join idb.FED_REG1 r1 on r1.ss = r2.ss
		where not exists (select * from [RDS].T_FR_DOCUMENT frd where frd.snils = r2.ss and ltrim(rtrim(r2.sn_dl)) = frd.Series and ltrim(rtrim(r2.nom_dl)) = frd.Number and b.BenefitsID = frd.Benefits )
		--and r1.CodeIzm <> '00'
		
		insert into [RDS].T_FR_RECORD 
			(
			 [FrRecordID]
			,[Snils]
			,[Deleted]
			,[DateBegin]
			,[DateEnd]
			,[NSU]
			,[DateNsuBegin]
			,[DateNsuEnd]
			,[Nsus]
			,[Version]
			,[UpdateDate]
		)
		select NEWID()
			,r1.ss
			,0
			,r1.DB_EDV
			,case 
				when cast( r1.DE_EDV as datetime) = cast( '19000101' as datetime) then null 
				else cast( r1.DE_EDV as datetime) 
			 end
			,case 
				when r1.prizn1 = 'Д' then 1 
				else 0 
			 end
			,case 
				when r1.prizn1 = 'Д' then	case 
												when DATEPART(YEAR, cast(d.DATE_BL as datetime)) <  DATEPART(YEAR, GETDATE()) and (cast(d.DATE_EL as datetime) = cast('19000101' as datetime) or DATEPART(YEAR, cast(d.DATE_EL as datetime)) >=  DATEPART(YEAR, GETDATE())) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '0101' 
												when  cast(d.DATE_BL as datetime) > cast(d.DATE_EL as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) =  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '0101' 
												else cast(d.DATE_BL as datetime) 
											end
				else null 
			 end
			,case 
				when r1.prizn1 = 'Д' then case
											when cast( d.DATE_EL as datetime) = cast( '19000101' as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) <=  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '1231' 
											when cast( d.DATE_EL as datetime) = cast( '19000101' as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) >  DATEPART(YEAR, GETDATE()) then cast(@datee as datetime)
											when DATEPART(YEAR, cast(d.DATE_EL as datetime)) >  DATEPART(YEAR, GETDATE()) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) <=  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '1231' 
											when  cast(d.DATE_BL as datetime) > cast(d.DATE_EL as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) =  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '1231'
											else cast( d.DATE_EL as datetime) 
										  end 
				else null 
			 end
			,case 
				when r1.prizn1Next = 'Д' then 1 
				else 0 
			 end
			,@ver
			,@CurrentDateTime
		from idb.FED_REG1 r1
			join RDS.T_FR_PATIENT fr  on r1.ss = fr.SNILS
			cross apply (
				select top 1 * from idb.FED_REG2 r2
				where r1.ss = r2.ss
				order by cast(r2.DATE_EL as datetime)
			) d
		where r1.CodeIzm <> '00'

		if(@Full = 1)
		begin
		insert into [RDS].T_FR_RECORD 
			(
			 FrRecordID
			,Snils
			,Deleted
			,DateBegin
			,DateEnd
			,NSU
			,DateNsuBegin
			,DateNsuEnd
			,Nsus
			,[Version]
			,UpdateDate
			)

		select NEWID()
			,fr.Snils
			,1
			,t.DateBegin
			,t.DateEnd
			,0
			,null
			,null
			,0
			,@ver
			,@CurrentDateTime
		from RDS.T_FR_PATIENT fr
		cross apply (
			select top 1 * 
			from [RDS].T_FR_RECORD rec 
			where rec.Snils = fr.Snils 
			order by rec.Version desc
		) t
		where not exists (select * from idb.FED_REG1 r1 where r1.ss = fr.SNILS) and t.Deleted = 0
		end

		insert into [RDS].T_FR_RECORD 
			(FrRecordID
			, Snils
			, Deleted
			, DateBegin
			, DateEnd
			, NSU
			, DateNsuBegin
			, DateNsuEnd
			, Nsus
			, [Version]
			, UpdateDate
		)
		select NEWID()
			,r1.ss
			,0
			,r1.DB_EDV
			,case 
				when cast( r1.DE_EDV as datetime) = cast( '19000101' as datetime) then null 
				else cast( r1.DE_EDV as datetime) 
			 end
			,case 
				when r1.prizn1 = 'Д' then 1 
				else 0 
			 end
			,case 
				when r1.prizn1 = 'Д' then case 
											when DATEPART(YEAR, cast(d.DATE_BL as datetime)) <  DATEPART(YEAR, GETDATE()) and (cast(d.DATE_EL as datetime) = cast('19000101' as datetime) or DATEPART(YEAR, cast(d.DATE_EL as datetime)) >=  DATEPART(YEAR, GETDATE())) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '0101' 
											when  cast(d.DATE_BL as datetime) > cast(d.DATE_EL as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) =  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '0101' 
											else cast(d.DATE_BL as datetime) 
										  end
				else null 
			 end
			,case 
				when r1.prizn1 = 'Д' then case 
											when cast( d.DATE_EL as datetime) = cast( '19000101' as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) <=  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '1231' 
											when cast( d.DATE_EL as datetime) = cast( '19000101' as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) >  DATEPART(YEAR, GETDATE()) then cast(@datee as datetime)
											when DATEPART(YEAR, cast(d.DATE_EL as datetime)) >  DATEPART(YEAR, GETDATE()) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) <=  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '1231' 
											when  cast(d.DATE_BL as datetime) > cast(d.DATE_EL as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) =  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '1231'
											else cast( d.DATE_EL as datetime) 
										  end 
				else null 
			 end
			,case 
				when r1.prizn1Next = 'Д' then 1 
				else 0 
			 end
			,@ver
			,@CurrentDateTime
		from idb.FED_REG1 r1
			join RDS.T_FR_PATIENT fr  on r1.ss = fr.SNILS 
			cross apply (
				select top 1 * from idb.FED_REG2 r2
				where r1.ss = r2.ss
				order by cast(r2.DATE_EL as datetime)
			) d
			cross apply (
				select top 1 * 
				from [RDS].T_FR_RECORD rec 
				where rec.Snils = r1.SS 
				order by rec.Version desc
			) t
		where r1.CodeIzm = '00' and (t.Deleted = 1 
										or t.DateBegin <> r1.DB_EDV 
										or (t.DateEnd is null and (case when cast( r1.DE_EDV as datetime) = cast( '19000101' as datetime) then null else cast( r1.DE_EDV as datetime) end) is not null)
										or (t.DateEnd is not null and (case when cast( r1.DE_EDV as datetime) = cast( '19000101' as datetime) then null else cast( r1.DE_EDV as datetime) end) is null)
										or ((t.DateEnd is not null and (case when cast( r1.DE_EDV as datetime) = cast( '19000101' as datetime) then null else cast( r1.DE_EDV as datetime) end) is not null) and t.DateEnd <> (case when cast( r1.DE_EDV as datetime) = cast( '19000101' as datetime) then null else cast( r1.DE_EDV as datetime) end))
										or t.NSU <> case when r1.prizn1 = 'Д' then 1 else 0 end
										or t.Nsus <> case when r1.prizn1Next = 'Д' then 1 else 0 end
										or t.DateNsuBegin <> cast(d.DATE_BL as datetime)
										or (t.DateNsuEnd is null and (case when cast( d.DATE_EL as datetime) = cast( '19000101' as datetime) then null else cast( d.DATE_EL as datetime) end)is not null)
										or (t.DateNsuEnd is not null and (case when cast( d.DATE_EL as datetime) = cast( '19000101' as datetime) then null else cast( d.DATE_EL as datetime) end)is null)
										or ((t.DateNsuEnd is not null and (case when cast( d.DATE_EL as datetime) = cast( '19000101' as datetime) then null else cast( d.DATE_EL as datetime) end)is not null) and t.DateNsuEnd <> (case when cast( d.DATE_EL as datetime) = cast( '19000101' as datetime) then null else cast( d.DATE_EL as datetime) end))
										)

		insert into [RDS].T_FR_RECORD (FrRecordID, Snils, Deleted, DateBegin, DateEnd, NSU, DateNsuBegin, DateNsuEnd, Nsus, [Version], UpdateDate
		)
		select NEWID()
			,r1.ss
			,0
			,r1.DB_EDV
			,case 
				when cast( r1.DE_EDV as datetime) = cast( '19000101' as datetime) then null 
				else cast( r1.DE_EDV as datetime) 
			 end
			,case 
				when r1.prizn1 = 'Д' then 1 
				else 0 
			 end
			,case 
				when r1.prizn1 = 'Д' then case 
											when DATEPART(YEAR, cast(d.DATE_BL as datetime)) <  DATEPART(YEAR, GETDATE()) and (cast(d.DATE_EL as datetime) = cast('19000101' as datetime) or DATEPART(YEAR, cast(d.DATE_EL as datetime)) >=  DATEPART(YEAR, GETDATE())) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '0101' 
											when  cast(d.DATE_BL as datetime) > cast(d.DATE_EL as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) =  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '0101' 
											else cast(d.DATE_BL as datetime) 
										  end
				else null 
			 end
			,case 
				when r1.prizn1 = 'Д' then case 
											when cast( d.DATE_EL as datetime) = cast( '19000101' as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) <=  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '1231' 
											when cast( d.DATE_EL as datetime) = cast( '19000101' as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) >  DATEPART(YEAR, GETDATE()) then cast(@datee as datetime)
											when DATEPART(YEAR, cast(d.DATE_EL as datetime)) >  DATEPART(YEAR, GETDATE()) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) <=  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '1231' 
											when  cast(d.DATE_BL as datetime) > cast(d.DATE_EL as datetime) and DATEPART(YEAR, cast(d.DATE_BL as datetime)) =  DATEPART(YEAR, GETDATE()) then cast(DATEPART(YEAR, GETDATE()) as varchar(4)) + '1231'
											else cast( d.DATE_EL as datetime) 
										  end 
				else null 
			 end
			,case 
				when r1.prizn1Next = 'Д' then 1 
				else 0 
			 end
			,@ver
			,@CurrentDateTime
		from idb.FED_REG1 r1
			join RDS.T_FR_PATIENT fr  on r1.ss = fr.SNILS 
			cross apply (
				select top 1 * from idb.FED_REG2 r2
				where r1.ss = r2.ss
				order by cast(r2.DATE_EL as datetime)
			) d
		where not exists (select * from [RDS].T_FR_RECORD r1 where r1.SNILS = fr.SNILS)

		-- обновление РР
		--exec sp_CloseRRPatient @ver
		

		--exec sp_FILL_FEDERAL_REGISTER_LOAD @ver

		drop table idb.FED_REG1
		drop table idb.FED_REG2
	end
END