if exists ( select * from sys.all_objects ao where ao.name = 'sp_MtrSchetLoad_Bulk_5_0' ) DROP PROCEDURE [dbo].[sp_MtrSchetLoad_Bulk_5_0]
GO

CREATE PROCEDURE [dbo].[sp_MtrSchetLoad_Bulk_5_0]
  @SchetID uniqueidentifier
 ,@type int
 ,@Worker uniqueidentifier 
AS 
BEGIN

update tempdb..TEMP_ZGLV_MTR set ID = '1'
update tempdb..TEMP_SCHET_MTR set ID = '1'
update tempdb..TEMP_ZAP_MTR set [SchetZapID] = newid()
update tempdb..TEMP_Z_SLUCH_MTR set [SchetSluchAccomplishedID] = newid()
update tempdb..TEMP_SANK_MTR set [SchetSluchSankID] = newid()
update tempdb..TEMP_SANK_SL_ID_MTR set [SchetSluchSankSLID] = newid() 
update tempdb..TEMP_SLUCH_MTR set [SchetSluchID] = newid()
update tempdb..TEMP_ONK_SL_MTR  set SchetSluchOnkID = newid()
update tempdb..TEMP_ONK_USL_MTR set [SchetOnkUslID] = newid() 
update tempdb..TEMP_SANK_MTR set [SchetSluchSankID] = newid() 
update tempdb..TEMP_KSG_KPG_MTR set [KsgID] = newid() 
update tempdb..TEMP_USL_MTR  set [SchetUslID] = newid()

declare @AdditionalSchetID uniqueidentifier, @isAdditional bit = 0
IF exists(select top 1 s.SchetID
			from tempdb..TEMP_SCHET_MTR schet 
			join tempdb..TEMP_ZGLV_MTR zglv on schet.ID = zglv.ID
			join ies.T_SCHET s with(nolock) on s.NSCHET = schet.NSCHET
											and s.C_OKATO1 = zglv.C_OKATO1 
											and s.DSCHET = schet.DSCHET
											--and s.[YEAR] = schet.[YEAR]
											--and s.[MONTH] = schet.[MONTH] 
											--and @type = 538
											and s.type_ = 538 
											and s.IsDelete = 0 
											and s.SchetKind = 0
											and ((s.[YEAR] = 2022 and s.[MONTH] >= 11) or s.[YEAR] > 2022)
											--and s.[VERSION] = '3.3'
)
BEGIN
set @isAdditional = 1
set @AdditionalSchetID = (select top 1 s.SchetID
							from tempdb..TEMP_SCHET_MTR schet 
							join tempdb..TEMP_ZGLV_MTR zglv on schet.ID = zglv.ID
							join ies.T_SCHET s with(nolock) on s.NSCHET = schet.NSCHET
															and s.C_OKATO1 = zglv.C_OKATO1
															and s.DSCHET = schet.DSCHET
															--and s.[YEAR] = schet.[YEAR] 
															--and s.[MONTH] = schet.[MONTH] 
															and s.type_ = 538
															and s.IsDelete = 0 
															and s.SchetKind = 0
															and ((s.[YEAR] = 2022 and s.[MONTH] >= 11) or s.[YEAR] > 2022)
															--and s.[VERSION] = '3.3'
															)
END


--Для ФЛК N - файлов присвоим 5381
--=================================
declare @type_N int
set @type_N = @type
if exists (select top 1 f.[FileName] from ies.T_SCHET_LOAD_HISTORY f where f.Schet = @SchetID and f.[FileName] like 'N[0-9]%')
begin
set @type_N = 5381
end
--=================================

create table #Errors
(
	OSHIB varchar(36),
	IM_POL varchar(10),
	ZN_POL varchar(100),
	NSCHET varchar(15),
	BAS_EL varchar(10),
	N_ZAP int,	
	IDCASE varchar(11),
	SL_ID varchar(36),
	IDSERV varchar(36),
	COMMENT varchar(250)
)

insert into #Errors (
OSHIB,
IM_POL,
ZN_POL,
NSCHET,
BAS_EL,
N_ZAP,
IDCASE,
SL_ID,
IDSERV,
COMMENT
)
exec sp_GetFlkFiltersBulkMtrSchet538_q015 @type_N

--проверка уникальности идентификаторов
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'N_ZAP', N_ZAP as ZN_POL, (select top 1 NSCHET from tempdb..TEMP_SCHET_MTR), 'ZAP', N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '905', 'Дублирующий идентификатор N_ZAP' from tempdb..TEMP_ZAP_MTR
group by N_ZAP
having count(*)>1

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'IDCASE', IDCASE as ZN_POL, (select top 1 NSCHET from tempdb..TEMP_SCHET_MTR), 'Z_SL', null as N_ZAP, IDCASE as IDCASE, null as SL_ID, null as IDSERV, '905', 'Дублирующий идентификатор IDCASE' from tempdb..TEMP_Z_SLUCH_MTR
group by IDCASE
having count(*)>1

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SL_ID', SL_ID as ZN_POL, (select top 1 NSCHET from tempdb..TEMP_SCHET_MTR), 'SL', null as N_ZAP, IDCASE, SL_ID, null as IDSERV, '905', 'Дублирующий идентификатор SL_ID, в пределах законченного случая' from tempdb..TEMP_SLUCH_MTR
group by IDCASE, SL_ID
having count(*)>1

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'IDSERV', IDSERV as ZN_POL, (select top 1 NSCHET from tempdb..TEMP_SCHET_MTR), 'USL', null as N_ZAP, IDCASE, SL_ID, IDSERV, '905', 'Дублирующий идентификатор IDSERV в пределах одного случая' from tempdb..TEMP_USL_MTR
where IDSERV is not null
group by IDCASE, SL_ID, IDSERV
having count(*)>1

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'S_CODE', S_CODE as ZN_POL, (select top 1 NSCHET from tempdb..TEMP_SCHET_MTR), 'SANK', null as N_ZAP, IDCASE, null as SL_ID, null as IDSERV, '905', 'Дублирующий идентификатор S_CODE, в пределах законченного случая' from tempdb..TEMP_SANK_MTR
group by IDCASE, S_CODE
having count(*)>1

--проверка, что суммы сходятся
--declare @sumsluch decimal(17,2)
--select @sumsluch =  Sum([SUM_M]) from tempdb..TEMP_SLUCH_MTR
--declare @sumZsluch decimal(17,2)
--select @sumZsluch =  Sum([SUMV]) from tempdb..TEMP_Z_SLUCH_MTR
--declare @sumschet decimal(17,2)
--select @sumschet = SUM([SUMMAV]) from tempdb..TEMP_SCHET_MTR
--if (@sumsluch <> @sumschet) 
--begin
--	insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
--	select 'SUMMAV', convert(varchar(17), @sumschet) as ZN_POL ,(select top 1 NSCHET from tempdb..TEMP_SCHET_MTR), 'SCHET', null as N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '905', 'Сумма на счете не равна сумме случаев'
--end
--else if (@sumZsluch <> @sumschet) 
--begin
--	insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
--	select 'SUMMAV', convert(varchar(17), @sumschet) as ZN_POL ,(select top 1 NSCHET from tempdb..TEMP_SCHET_MTR), 'SCHET', null as N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '905', 'Сумма на счете не равна сумме закочненных случаев'
--end
--else if (@sumZsluch <> @sumsluch) 
--begin
--	insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
--	select 'SUMV', convert(varchar(17), @sumZsluch) as ZN_POL ,(select top 1 NSCHET from tempdb..TEMP_SCHET_MTR), 'Z_SL', null as N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV,  '905', 'Сумма закочненных случаев не равна сумме случаев'
--end

--001F.00.0680 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'S_OSN', t0.S_OSN as ZN_POL,(select top 1 NSCHET from tempdb..TEMP_SCHET_MTR), 'SANK', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0680', 'Значение не найдено в справочнике F014'
from tempdb..TEMP_SANK_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_F014_DENY_REASON t2 with(nolock) on t2.Kod = t0.S_OSN and DATEBEG<=isnull(t0.DATE_ACT,t1.DATE_Z_2) and (DATEEND is null or DATEEND>=isnull(t0.DATE_ACT,t1.DATE_Z_2))
where t0.S_OSN is not null and t2.Kod is null
--001F.00.0680 end

IF EXISTS (SELECT TOP 1 1 FROM #Errors)
BEGIN
SELECT 
'<?xml version="1.0" encoding="Windows-1251"?>
' + (
SELECT 
	OSHIB AS 'OSHIB',
	IM_POL AS 'IM_POL',
	ZN_POL AS 'ZN_POL',
	NSCHET AS 'NSCHET',
	BAS_EL AS 'BAS_EL',
	N_ZAP AS 'N_ZAP',
	IDCASE AS 'IDCASE',
	SL_ID AS 'SL_ID',
	IDSERV AS 'IDSERV',
	COMMENT AS 'COMMENT'
FROM #Errors
FOR XML PATH('PR'),
ROOT('FLK_P')
)
END

ELSE
BEGIN

IF @isAdditional = 1
BEGIN
EXEC sp_MtrSchetLoadisAdditional_Bulk_3_3 @AdditionalSchetID
END

ELSE
BEGIN

SELECT '[IES].[T_SCHET]' as tableName, ' информации о счете' as operationName, (select count(*) from tempdb..TEMP_SCHET_MTR) as tableCount
	 
SELECT 
 zglv.[VERSION] AS [VERSION]
,zglv.[FILENAME] AS [FILENAME]
,zglv.[DATA] AS [DATA]
,zglv.[C_OKATO1] AS [C_OKATO1]
,zglv.[OKATO_OMS] AS [OKATO_OMS]
,schet.[CODE] AS [CODE]
,schet.[YEAR] AS [YEAR]
,schet.[MONTH] AS [MONTH]
,schet.[NSCHET] AS [NSCHET]
,schet.[DSCHET] AS [DSCHET]
,schet.[SUMMAV] AS [SUMMAV]
,schet.[COMENTS] AS [COMENTS]
,schet.[SUMMAP] AS [SUMMAP]
,schet.[SANK_MEK] AS [SANK_MEK]
,schet.[SANK_MEE] AS [SANK_MEE]
,schet.[SANK_EKMP] AS [SANK_EKMP]
,CAST(CAST(CAST(GETDATE() as float) as INT) as datetime) AS [ReceivedDate]
,GETDATE() AS [ReceivedTime] 
,@Worker AS [Worker]
,@SchetID AS [SchetID]
,538 AS [type_]
,0 AS [Status]
,0 AS [SchetKind]
FROM tempdb..TEMP_SCHET_MTR schet  
JOIN tempdb..TEMP_ZGLV_MTR zglv on (zglv.ID = schet.ID)

SELECT '[IES].[T_SCHET_ZAP]' as tableName, ' информации о людях' as operationName, (select count(*) from tempdb..TEMP_ZAP_MTR) as tableCount

select t0.[N_ZAP], Left(t0.rezult,Len(t0.rezult)-1) As rezult into #temp_dost
	From
    (
        Select distinct cast([N_ZAP] as varchar(36)) as [N_ZAP], 
            (
                Select cast(t1.[N_ZAP] as varchar(1)) + ',' AS [text()]
                From tempdb..TEMP_DOST_MTR t1
                Where cast(t1.[N_ZAP] as varchar(36)) = cast(ST2.[N_ZAP] as varchar(36))
                ORDER BY cast(t1.[N_ZAP] as varchar(36))
                For XML PATH ('')
            ) rezult
        From tempdb..TEMP_DOST_MTR ST2
    ) t0 

select t0.[N_ZAP], Left(t0.rezult,Len(t0.rezult)-1) As rezult into #temp_dost_p
	From
    (
        Select distinct cast([N_ZAP] as varchar(36)) as [N_ZAP], 
            (
                Select cast(t1.[N_ZAP] as varchar(1)) + ',' AS [text()]
                From tempdb..TEMP_DOST_P_MTR t1
                Where cast(t1.[N_ZAP] as varchar(36)) = cast(ST2.[N_ZAP] as varchar(36))
                ORDER BY cast(t1.[N_ZAP] as varchar(36))
                For XML PATH ('')
            ) rezult
        From tempdb..TEMP_DOST_P_MTR ST2
    ) t0 	

SELECT 
 zap.[SchetZapID] AS [SchetZapID]
,zap.[N_ZAP] AS [N_ZAP]
,zap.[NSCHET_MO] AS [NSCHET_MO]
,zap.[DSCHET_MO] AS [DSCHET_MO]
,zap.[YEAR_MO] AS [YEAR_MO]
,zap.[MONTH_MO] AS [MONTH_MO]
,@SchetID AS [Schet]
,505 AS [type_]
,pacient.[VPOLIS] AS [VPOLIS]
,pacient.[SPOLIS] AS [SPOLIS]
,pacient.[NPOLIS] AS [NPOLIS]
,pacient.[ENP] AS [ENP]
,pacient.[ST_OKATO] AS [ST_OKATO]
,pacient.[FAM] AS [FAM]
,pacient.[IM] AS [IM]
,pacient.[OT] AS [OT]
,pacient.[W] AS [W]
,pacient.[DR] AS [DR]
,dost.[rezult] AS [DOST]
,pacient.[FAM_P] AS [FAM_P]
,pacient.[IM_P] AS [IM_P]
,pacient.[OT_P] AS [OT_P]
,pacient.[W_P] AS [W_P]
,pacient.[DR_P] AS [DR_P]
,dost_p.[rezult] AS [DOST_P]
,pacient.[MR] AS [MR]
,pacient.[DOCTYPE] AS [DOCTYPE]
,pacient.[DOCSER] AS [DOCSER]
,pacient.[DOCNUM] AS [DOCNUM]
,pacient.[DOCDATE] AS [DOCDATE]
,pacient.[DOCORG] AS [DOCORG]
,pacient.[SNILS] AS [SNILS]
,pacient.[OKATOG] AS [OKATOG]
,pacient.[OKATOP] AS [OKATOP]
,pacient.[NOVOR] AS [NOVOR]
,pacient.[VNOV_D] AS [VNOV_D]
,pacient.[COMENTP] AS [COMENTP]
,pacient.[SOC] AS [SOC]
,pacient.[NEXT_D] AS [NEXT_D]
,(select top 1 F032TrmoID from ies.T_F032_TRMO f032 where f032.mcod = pacient.[MO_PR] and f032.D_BEGIN<=zSL.DATE_Z_2 and (f032.D_END is null or f032.D_END>=zSL.DATE_Z_2)) as [MO_PR]
,(select top 1 V039EmpTypeID from ies.T_V039_EMP_TYPE v039 where v039.ID_VZ = pacient.[VZ] and v039.DATEBEG<=zSL.DATE_Z_2 and (v039.DATEEND is null or v039.DATEEND>=zSL.DATE_Z_2)) as [VZ]
FROM tempdb..TEMP_ZAP_MTR zap
JOIN tempdb..TEMP_PACIENT_MTR pacient ON (pacient.[N_ZAP] = zap.[N_ZAP])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (zap.[N_ZAP] = zSL.[N_ZAP])
LEFT JOIN #temp_dost   dost   ON (pacient.[N_ZAP] = dost.[N_ZAP])
LEFT JOIN #temp_dost_p dost_p ON (pacient.[N_ZAP] = dost_p.[N_ZAP])

DROP TABLE #temp_dost   
DROP TABLE #temp_dost_p

SELECT '[IES].[T_SCHET_SLUCH_ACCOMPLISHED]' as tableName, ' информации о законченных случаях лечения' as operationName, (select count(*) from tempdb..TEMP_Z_SLUCH_MTR) as tableCount

select t0.IDCASE, Left(t0.rezult,Len(t0.rezult)-1) As rezult into #OS_SLUCH
	From
    (
        Select distinct cast(IDCASE as varchar(36)) as IDCASE, 
            (
                Select cast(t1.OS_SLUCH as varchar(1)) + ',' AS [text()]
                From tempdb..TEMP_OS_SLUCH_MTR t1
                Where t1.IDCASE = ST2.IDCASE
                ORDER BY t1.IDCASE
                For XML PATH ('')
            ) rezult
        From tempdb..TEMP_OS_SLUCH_MTR ST2
    ) t0

SELECT 
 zSL.[SchetSluchAccomplishedID] AS [SchetSluchAccomplishedID]
,zap.[SchetZapID] AS [SchetZap]
,zSL.[IDCASE] AS [IDCASE]
,zSL.[USL_OK] AS [USL_OK]
,zSL.[VIDPOM] AS [VIDPOM]
,zSL.[FOR_POM] AS [FOR_POM]
,zSL.[NPR_MO] AS [NPR_MO]
,zSL.[NPR_DATE] AS [NPR_DATE]
,zSL.[NPR_NUM] AS [NPR_NUM]
,zSL.[P_DISP2] AS [P_DISP2]
,zSL.[LPU] AS [LPU]
,zSL.[DATE_Z_1] AS [DATE_Z_1]
,zSL.[DATE_Z_2] AS [DATE_Z_2]
,zSL.[KD_Z] AS [KD_Z]
,zSL.[VNOV_M] AS [VNOV_M]
,zSL.[RSLT] AS [RSLT]
,zSL.[ISHOD] AS [ISHOD]
,os.[rezult] AS [OS_SLUCH]
,zSL.[VB_P] AS [VB_P]
,zSL.[IDSP] AS [IDSP]
,zSL.[SUMV] AS [SUMV]
,zSL.[OPLATA] AS [OPLATA]
,zSL.[SUMP] AS [SUMP]
,zSL.[SANK_IT] AS [SANK_IT]
,0 as [MedicalExpertiseStatus]
FROM tempdb..TEMP_Z_SLUCH_MTR zSL 
JOIN tempdb..TEMP_ZAP_MTR zap ON (zap.[N_ZAP] = zSL.[N_ZAP])
LEFT JOIN #OS_SLUCH os on os.IDCASE = zSL.IDCASE

DROP TABLE #OS_SLUCH

SELECT '[IES].[T_SCHET_SLUCH]' as tableName, ' информации о случаях лечения' as operationName, (select count(*) from tempdb..TEMP_SLUCH_MTR) as tableCount

SELECT 
 sl.[SchetSluchID] AS [SchetSluchID]
,zap.[SchetZapID] AS [SchetZap]
,zSL.[SchetSluchAccomplishedID] AS [SchetSluchAccomplished]
,506 AS [type_]
,sl.[SL_ID] AS [SL_ID]
,sl.[VID_HMP] AS [VID_HMP]
,(select top 1 v019.V019VmpMethodID from IES.T_V019_VMP_METHOD v019 where sl.METOD_HMP = v019.IDHM and v019.DATEBEG<=zSL.DATE_Z_2 and (v019.DATEEND is null or v019.DATEEND>=zSL.DATE_Z_2)) AS [METOD_HMP]
,sl.[LPU_1] AS [LPU_1]
,sl.[PROFIL] AS [PROFIL]
,(select top 1 v020.[V020BedProfileId] from [IES].[T_V020_BED_PROFILE] v020 where sl.[PROFIL_K] = v020.[IDK_PR] and v020.[DATEBEG]<=zSL.[DATE_Z_2] and (v020.[DATEEND] is null or v020.[DATEEND] >= zSL.[DATE_Z_2])) AS [PROFIL_K]
,sl.[DET] AS [DET]
,(select top 1 v025.[V025KpcID] from [IES].[T_V025_KPC] v025 where v025.[IDPC] = sl.[P_CEL] and v025.[DATEBEG]<=zSL.[DATE_Z_2] and (v025.[DATEEND] is null or v025.[DATEEND] >= zSL.[DATE_Z_2])) AS [P_CEL]
,(select top 1 v040.[V040ApplPlaceID] from ies.T_V040_APPL_PLACE v040 where sl.[MOP] = v040.[ID_MOP] and v040.DATEBEG<=zSL.DATE_Z_2 and (v040.DATEEND is null or v040.DATEEND>=zSL.DATE_Z_2)) as [MOP]
,sl.[DISP] AS [DISP]
,sl.[TAL_D] AS [TAL_D]
,case when @type_N = 5381 then 'Нет' else sl.[NHISTORY] end AS [NHISTORY]
,sl.[DATE_1] AS [DATE_1]
,sl.[DATE_2] AS [DATE_2]
,sl.[KD] AS [KD]
,sl.[DS0] AS [DS0]
,sl.[DS1] AS [DS1]
,(select top 1 v027.[V027CZabID] from [IES].[T_V027_C_ZAB] v027 where v027.[IDCZ] = sl.[C_ZAB] and v027.[DATEBEG]<=zSL.[DATE_Z_2] and (v027.[DATEEND] is null or v027.[DATEEND] >= zSL.[DATE_Z_2])) AS [C_ZAB]
,sl.[DS_ONK] AS [DS_ONK]
,sl.[DN] AS [DN]
,sl.[CODE_MES1] AS [CODE_MES1]
,sl.[CODE_MES2] AS [CODE_MES2]
,sl.[REAB] AS [REAB]
,(SELECT TOP 1 v021.[IDSPEC] FROM [IES].[T_V021_MED_SPEC] v021 WHERE v021.[CODE_SPEC]=sl.[PRVS] AND v021.[DATEBEG]<=zSL.[DATE_Z_2] AND (v021.[DATEEND] IS NULL OR v021.[DATEEND]>=zSL.[DATE_Z_2])) AS [PRVS3]
,sl.[VERS_SPEC] AS [VERS_SPEC]
,sl.[ED_COL] AS [ED_COL]
,sl.[TARIF] AS [TARIF]
,sl.[SUM_M] AS [SUMV]
,sl.[SUM_M] AS [SUMP]
,sl.[COMENTSL] AS [COMENTSL]
FROM tempdb..TEMP_SLUCH_MTR sl    
JOIN tempdb..TEMP_ZAP_MTR zap ON (zap.[N_ZAP] = sl.[N_ZAP])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

IF(@type = 538) --Входящий счет по МТР основной
BEGIN
	SELECT '[IES].[T_SCHET_RESULTS]' as tableName, ' информации о результатах лечения' as operationName, (select count(*) from tempdb..TEMP_SLUCH_MTR) as tableCount

	SELECT
	 NEWID() AS [SchetResultsID]
	,@SchetID AS [MtrSchetIn]
	,sl.[SchetSluchID] AS [SchetSluchMain]
	,null AS [SchetSluchAccepted]
	FROM tempdb..TEMP_SLUCH_MTR sl
	JOIN tempdb..TEMP_ZAP_MTR zap on (zap.[N_ZAP] = sl.[N_ZAP])
END

declare @cnt int

select @cnt = count(*)
from
(
	SELECT NEWID() as [SchetSluchDsID], t1.[DS2] as [MKB], t2.[SchetSluchID] as [SchetSluch],0 as [MKBType]
	FROM 
	tempdb..TEMP_DS2_MTR  t1
	JOIN tempdb..TEMP_SLUCH_MTR t2 on (t2.[IDCASE] = t1.[IDCASE])
	union
	SELECT NEWID() as [SchetSluchDsID], t1.[DS3] as [MKB], t2.[SchetSluchID] as [SchetSluch],1 as [MKBType]
	FROM 
	tempdb..TEMP_DS3_MTR  t1
	JOIN tempdb..TEMP_SLUCH_MTR t2 on (t2.[IDCASE] = t1.[IDCASE])
)aa

SELECT '[IES].[T_SCHET_SLUCH_DS]' as tableName, ' информации об диагнозах' as operationName, @cnt as tableCount

SELECT NEWID() as [SchetSluchDsID], t1.[DS2] as [MKB], t2.[SchetSluchID] as [SchetSluch],0 as [MKBType], null as [DS2_PR], null as [PR_DS2_N]
FROM 
tempdb..TEMP_DS2_MTR  t1
JOIN tempdb..TEMP_SLUCH_MTR t2 on (t2.[IDCASE] = t1.[IDCASE] and t1.[SL_ID] = t2.[SL_ID])
union
SELECT NEWID() as [SchetSluchDsID], t1.[DS3] as [MKB], t2.[SchetSluchID] as [SchetSluch],1 as [MKBType], null as [DS2_PR], null as [PR_DS2_N]
FROM 
tempdb..TEMP_DS3_MTR  t1
JOIN tempdb..TEMP_SLUCH_MTR t2 on (t2.[IDCASE] = t1.[IDCASE] and t1.[SL_ID] = t2.[SL_ID])
union
SELECT NEWID() as [SchetSluchDsID], t1.[DS2] as [MKB], t2.[SchetSluchID] as [SchetSluch],0 as [MKBType], [DS2_PR] as [DS2_PR], [PR_DS2_N] as [PR_DS2_N]
FROM 
tempdb..TEMP_DS2_N_MTR  t1
JOIN tempdb..TEMP_SLUCH_MTR t2 on (t2.[IDCASE] = t1.[IDCASE] and t1.[SL_ID] = t2.[SL_ID])

SELECT '[IES].[T_SCHET_USL_NAPR]' as tableName, ' направлений' as operationName, (select count(*) from tempdb..TEMP_NAPR_MTR) as tableCount

SELECT 
 NEWID() AS [SchetUslNaprID]
,sl.[SchetSluchID] AS [SchetSluch]
,napr.[NAPR_DATE] AS [NAPR_DATE]
,napr.[NAPR_MO] AS [NAPR_MO]
,napr.[NAPR_V] AS [NAPR_V]
,napr.[MET_ISSL] AS [MET_ISSL]
,napr.[NAPR_USL] AS [NAPR_USL]
FROM tempdb..TEMP_NAPR_MTR napr 
JOIN tempdb..TEMP_SLUCH_MTR sl ON (napr.[IDCASE] = sl.[IDCASE] and napr.[SL_ID] = sl.[SL_ID]) 
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

SELECT '[IES].[T_SCHET_SLUCH_CONS]' as tableName, ' сведений о проведении консилиума' as operationName, (select count(*) from tempdb..TEMP_CONS_MTR ) as tableCount

SELECT 
 NEWID() AS [SchetSluchConsID]
,sl.[SchetSluchID] AS [SchetSluch]
,(select top 1 N019.[N019OnkConsID] from [IES].[T_N019_ONK_CONS] N019 where N019.[ID_CONS] = cons.[PR_CONS] and N019.[DATEBEG]<=zSL.[DATE_Z_2] and (N019.[DATEEND] is null or N019.[DATEEND]>=zSL.[DATE_Z_2])) AS [N019OnkCons]
,cons.[DT_CONS] AS [DT_CONS]
FROM tempdb..TEMP_CONS_MTR cons
JOIN tempdb..TEMP_SLUCH_MTR sl ON (cons.[IDCASE] = sl.[IDCASE] and cons.[SL_ID] = sl.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

SELECT '[IES].[T_SCHET_SLUCH_ONK]' as tableName, ' онкологических заболеваний' as operationName, (select count(*) from tempdb..TEMP_ONK_SL_MTR) as tableCount

SELECT 
 onkSL.[SchetSluchOnkID] AS [SchetSluchOnkID]
,sl.[SchetSluchID] AS [SchetSluch]
,(select top 1 N018.[N018OnkReasID] from [IES].[T_N018_ONK_REAS] N018 where n018.[ID_REAS] = onkSL.[DS1_T] and n018.[DATEBEG]<=zSL.[DATE_Z_2] and (n018.[DATEEND] is null or n018.[DATEEND]>=zSL.[DATE_Z_2])) AS [DS1_T]
,(select top 1 N002.[N002StadiumID] from [IES].[T_N002_STADIUM] N002 where N002.[ID_St] = onkSL.[STAD] and N002.[DATEBEG]<=zSL.[DATE_Z_2] and (N002.[DATEEND] is null or N002.[DATEEND]>=zSL.[DATE_Z_2])) AS [STAD]
,(select top 1 N003.[N003TumorID] from [IES].[T_N003_TUMOR] N003 where N003.[ID_T] = onkSL.[ONK_T] and N003.[DATEBEG]<=zSL.[DATE_Z_2] and (N003.[DATEEND] is null or N003.[DATEEND]>=zSL.[DATE_Z_2])) AS [ONK_T]
,(select top 1 N004.[N004NodusID] from [IES].[T_N004_NODUS] N004 where N004.[ID_N] = onkSL.[ONK_N] and N004.[DATEBEG]<=zSL.[DATE_Z_2] and (N004.[DATEEND] is null or N004.[DATEEND]>=zSL.[DATE_Z_2])) AS [ONK_N]
,(select top 1 N005.[N005MetastasisID] from [IES].[T_N005_METASTASIS] N005 where N005.[ID_M] = onkSL.[ONK_M] and N005.[DATEBEG]<=zSL.[DATE_Z_2] and (N005.[DATEEND] is null or N005.[DATEEND]>=zSL.[DATE_Z_2])) AS [ONK_M]
,onkSL.[MTSTZ]
,onkSL.[SOD]
,onkSL.[K_FR]
,onkSL.[WEI]
,onkSL.[HEI]
,onkSL.[BSA]
FROM tempdb..TEMP_ONK_SL_MTR onkSL   
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = onkSL.[IDCASE] and sl.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

SELECT '[IES].[T_SCHET_SLUCH_ONK_DIAG]' as tableName, ' диагностического блока' as operationName, (select count(*) from tempdb..TEMP_B_DIAG_MTR) as tableCount

SELECT 
 NEWID() AS [SchetSluchOnkDiagID]
,onkSL.[SchetSluchOnkID] AS [SchetSluchOnk]
,diag.[DIAG_TIP] AS [DIAG_TIP]
,diag.[DIAG_CODE] AS [DIAG_CODE]
,diag.[DIAG_RSLT] AS [DIAG_RSLT]
,diag.[REC_RSLT] AS [REC_RSLT]
,diag.[DIAG_DATE] AS [DIAG_DATE]
FROM tempdb..TEMP_B_DIAG_MTR diag 
JOIN tempdb..TEMP_ONK_SL_MTR onkSL ON (diag.[IDCASE] = onkSL.[IDCASE] and diag.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = onkSL.[IDCASE] and sl.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

SELECT '[IES].[T_SCHET_SLUCH_ONK_PROT]' as tableName, ' сведения об имеющихся противопоказаниях' as operationName, (select count(*) from tempdb..TEMP_B_PROT_MTR) as tableCount

SELECT 
 NEWID() AS [SchetSluchOnkProtID]
,onkSL.[SchetSluchOnkID] AS [SchetSluchOnk]
,prot.[D_PROT] AS [D_PROT]
,(select top 1 N001.[N001PrOtID] from [IES].[T_N001_PrOt] N001 where N001.[ID_PrOt] = prot.[PROT] and N001.[DATEBEG]<=zSL.[DATE_Z_2] and (N001.[DATEEND] is null or N001.[DATEEND]>=zSL.[DATE_Z_2])) AS [PROT]
FROM tempdb..TEMP_B_PROT_MTR prot
JOIN tempdb..TEMP_ONK_SL_MTR onkSL ON (prot.[IDCASE] = onkSL.[IDCASE] and prot.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = onkSL.[IDCASE] and sl.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

SELECT '[IES].[T_SCHET_USL_ONK]' as tableName, ' сведений об услуге при лечении онкологического заболевания' as operationName, (select count(*) from tempdb..TEMP_ONK_USL_MTR) as tableCount

SELECT 
 onkusl.[SchetOnkUslID] AS [SchetUslOnkID]
,onkSL.[SchetSluchOnkID] AS [SchetSluchOnk]
,(select top 1 N013.[N013TreatTypeID] from [IES].[T_N013_TREAT_TYPE] N013 where N013.[ID_TLech] = onkusl.[USL_TIP] and N013.[DATEBEG]<=zSL.[DATE_Z_2] and (N013.[DATEEND] is null or N013.[DATEEND]>=zSL.[DATE_Z_2])) AS [USL_TIP]
,(select top 1 N014.[N014SurgTreatID] from [IES].[T_N014_SURG_TREAT] N014 where N014.[ID_THir] = onkusl.[HIR_TIP] and N014.[DATEBEG]<=zSL.[DATE_Z_2] and (N014.[DATEEND] is null or N014.[DATEEND]>=zSL.[DATE_Z_2])) AS [HIR_TIP]
,(select top 1 N015.[N015DrugTherapyLinesID] from [IES].[T_N015_DRUG_THERAPY_LINES] N015 where N015.[ID_TLek_L] = onkusl.[LEK_TIP_L] and N015.[DATEBEG]<=zSL.[DATE_Z_2] and (N015.[DATEEND] is null or N015.[DATEEND]>=zSL.[DATE_Z_2])) AS [LEK_TIP_L]
,(select top 1 N016.[N016DrugTherapyCyclesID] from [IES].[T_N016_DRUG_THERAPY_CYCLES] N016 where N016.ID_TLek_V = onkusl.[LEK_TIP_V] and N016.[DATEBEG]<=zSL.[DATE_Z_2] and (N016.[DATEEND] is null or N016.[DATEEND]>=zSL.[DATE_Z_2])) AS [LEK_TIP_V]
,(select top 1 N017.[N017RadiationTherapyTypesID] from [IES].[T_N017_RADIATION_THERAPY_TYPES] N017 where N017.[ID_TLuch] = onkusl.[LUCH_TIP] and N017.[DATEBEG]<=zSL.[DATE_Z_2] and (N017.[DATEEND] is null or N017.[DATEEND]>=zSL.[DATE_Z_2])) AS [LUCH_TIP]
,onkusl.[PPTR] AS [PPTR]
FROM tempdb..TEMP_ONK_USL_MTR onkusl
JOIN tempdb..TEMP_ONK_SL_MTR onkSL ON (onkSL.[IDCASE] = onkusl.[IDCASE] and onkSL.[SL_ID] = onkusl.[SL_ID])
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = onkSL.[IDCASE] and sl.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

select NEWID() as ID, t0.[IDCASE], t0.[SL_ID], t0.[REGNUM], t0.[CODE_SH], Left(t0.rezult,Len(t0.rezult)-1) As rezult 
into #temp_lek_pr
	From
	(
		Select distinct cast(t2.[IDCASE] as varchar(36)) as [IDCASE], cast(t2.[SL_ID] as varchar(36)) as [SL_ID], cast(t2.[REGNUM] as varchar(6)) as [REGNUM], cast(t2.[CODE_SH] as varchar(10)) as [CODE_SH],
			(
				Select cast(t1.[DATE_INJ] as varchar(40)) + ',' AS [text()]
				From tempdb..TEMP_LEK_PR_DATE_INJ_MTR t1
				Where (t1.[IDCASE] = t2.[IDCASE] and t1.[SL_ID] = t2.[SL_ID] and t1.[REGNUM] = t2.[REGNUM] and t1.[CODE_SH] = t2.[CODE_SH])
				ORDER BY t1.[REGNUM], t1.[CODE_SH]
				For XML PATH ('')
			) rezult
		From tempdb..TEMP_LEK_PR_DATE_INJ_MTR t2
	) t0 	

SELECT '[IES].[T_SCHET_USL_ONK_LEK_PR]' as tableName, ' сведений о лекарственных средствах' as operationName, (select count(*) from tempdb..TEMP_LEK_PR_DATE_INJ_MTR) as tableCount

SELECT 
 ID AS [SchetUslOnkLekPrID]
--,lekpr.[rezult] AS [DATE_INJ]
,aa.[SchetOnkUslID] AS [SchetUslOnk]
,(select top 1 N020.[N020OnkLekpID] from [IES].[T_N020_ONK_LEKP] N020 where lekpr.[REGNUM] = N020.[ID_LEKP] and N020.[DATEBEG]<=zSL.[DATE_Z_2] and (N020.[DATEEND] is null or N020.[DATEEND]>=zSL.[DATE_Z_2])) AS [REGNUM]
,(select top 1 v024.[V024DopKrID] from [IES].[T_V024_DOP_KR] v024 where lekpr.[CODE_SH] = v024.[IDDKK] and v024.[DATEBEG]<=zSL.[DATE_Z_2] and (v024.[DATEEND] is null or v024.[DATEEND]>=zSL.[DATE_Z_2])) AS [V024DopKr]
,lekpr.[REGNUM] AS [N020_R]
FROM #temp_lek_pr lekpr
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = lekpr.[IDCASE] and sl.[SL_ID] = lekpr.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])
cross apply (select top 1 * from tempdb..TEMP_ONK_USL_MTR onkusl where lekpr.[IDCASE] = onkusl.[IDCASE] and lekpr.[SL_ID] = onkusl.[SL_ID] and (onkusl.[USL_TIP] = 2 or onkusl.[USL_TIP] = 4)) aa

SELECT '[IES].[T_SCHET_USL_ONK_LEK_PR_DATE]' as tableName, ' сведений о датах лекарственных средств' as operationName, (select count(*) from tempdb..TEMP_LEK_PR_DATE_INJ_MTR) as tableCount

SELECT 
 NEWID() AS [SchetUslOnkLekPrDateID]
,lekpr.ID AS [SchetUslOnkLekPr]
,dateLek.[DATE_INJ] AS [DATE_INJ]
FROM tempdb..TEMP_LEK_PR_DATE_INJ_MTR dateLek
JOIN #temp_lek_pr lekpr ON (dateLek.[IDCASE] = lekpr.[IDCASE] and dateLek.[SL_ID] = lekpr.[SL_ID] and dateLek.[REGNUM] = lekpr.[REGNUM] and dateLek.[CODE_SH] = lekpr.[CODE_SH])

DROP TABLE #temp_lek_pr

SELECT '[IES].[T_KSG]' as tableName, ' информации о КСГ в случаях' as operationName, (select count(*) from tempdb..TEMP_KSG_KPG_MTR) as tableCount

SELECT 
 ksg.[KsgID] AS [KsgID]
,sl.[SchetSluchID] as [SchetSluch]
,ksg.[N_KSG] AS [N_KSG]
,ksg.[VER_KSG] AS [VER_KSG]
,ksg.[K_ZP] AS [K_ZP]
,ksg.[KSG_PG] AS [KSG_PG]
,(select top 1 v026.[V026KpgID] from IES.T_V026_KPG v026 where v026.[K_KPG] = ksg.[N_KPG] and v026.[DATEBEG]<=zSL.[DATE_Z_2] and (v026.[DATEEND] is null or v026.[DATEEND]>=zSL.[DATE_Z_2])) AS [N_KPG]
,ksg.[KOEF_Z] AS [KOEF_Z]
,ksg.[KOEF_UP] AS [KOEF_UP]
,ksg.[BZTSZ] AS [BZTSZ]
,ksg.[KOEF_D] AS [KOEF_D]
,ksg.[KOEF_U] AS [KOEF_U]
,ksg.[SL_K] AS [SL_K]
,ksg.[IT_SL] AS [IT_SL]
,(select top 1 v042.[V042AbortSlPayReasonID] from ies.T_V042_ABORT_SL_PAY_REASON v042 where v042.[ID_PR] = ksg.[PR_PR] and v042.DATEBEG<=zSL.DATE_Z_2 and (v042.DATEEND is null or v042.DATEEND>=zSL.DATE_Z_2)) as [PR_PR]
,ksg.[KOEF_PR] AS [KOEF_PR]
FROM tempdb..TEMP_KSG_KPG_MTR ksg 
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[SL_ID] = ksg.[SL_ID] and ksg.[IDCASE] = sl.[IDCASE])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (zSL.[IDCASE] = sl.[IDCASE])

SELECT '[IES].[T_KSG_CRIT]' as tableName, ' классификационного критерия CRIT(V024)' as operationName, (select count(*) from tempdb..TEMP_CRIT_MTR) as tableCount

SELECT 
 NEWID() AS [KsgCritID]
,ksg.[KsgID] AS [Ksg]
,(select top 1 v024.[V024DopKrID] from [IES].[T_V024_DOP_KR] v024 where v024.[IDDKK] = crit.[CRIT] and v024.[DATEBEG]<=zSL.[DATE_Z_2] and (v024.[DATEEND] is null or v024.[DATEEND]>=zSL.[DATE_Z_2])) AS [V024DopKr]
FROM tempdb..TEMP_CRIT_MTR crit
JOIN tempdb..TEMP_KSG_KPG_MTR ksg ON (ksg.[SL_ID] = crit.[SL_ID] and ksg.[IDCASE] = crit.[IDCASE])
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[SL_ID] = ksg.[SL_ID] and sl.[IDCASE] = ksg.[IDCASE])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (zSL.[IDCASE] = sl.[IDCASE])

SELECT '[IES].[T_KSLP]' as tableName, ' информации о КСЛП в случаях' as operationName, (select count(*) from tempdb..TEMP_SL_KOEF_MTR ) as tableCount

SELECT 
 NEWID() AS [KslpID]
,koef.[Z_SL] AS [KOEF]
--,koef.[IDSL] AS [KOEF_TYPE]
,(select top 1 v041.[V041TreatComplCoefID] from ies.T_V041_TREAT_COMPL_COEF v041 where v041.[ID_SL] = koef.[IDSL] and v041.DATEBEG<=zSL.DATE_Z_2 and (v041.DATEEND is null or v041.DATEEND>=zSL.DATE_Z_2)) as [IDSL]
,ksg.[KsgID] AS [Ksg]
--,sl.[SchetSluchID] AS [OmsSchetSluch]
FROM tempdb..TEMP_SL_KOEF_MTR koef
JOIN tempdb..TEMP_KSG_KPG_MTR ksg ON (ksg.[SL_ID] = koef.[SL_ID] and ksg.[IDCASE] = koef.[IDCASE])
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[SL_ID] = ksg.[SL_ID] and sl.[IDCASE] = ksg.[IDCASE])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (zSL.[IDCASE] = sl.[IDCASE])

SELECT '[IES].[T_SCHET_USL]' as tableName, ' информации об услугах' as operationName, (select count(*) from tempdb..TEMP_USL_MTR) as tableCount

SELECT 
 usl.[SchetUslID] AS [SchetUslID]
,507 AS [type_]
,sl.[SchetSluchID] AS [SchetSluch]
,usl.[IDSERV] AS [IDSERV]
,usl.[LPU] AS [LPU]
,usl.[LPU_1] AS [LPU_1]
,usl.[PROFIL] AS [PROFIL]
,usl.[VID_VME] AS [VID_VME]
,usl.[DET] AS [DET]
,usl.[DATE_IN] AS [DATE_IN]
,usl.[DATE_OUT] AS [DATE_OUT]
,usl.[DS] AS [DS]
,usl.[CODE_USL] AS [CODE_USL]
,usl.[USL] AS [USL]
,usl.[KOL_USL] AS [KOL_USL]
,usl.[TARIF] AS [TARIF]
,usl.[SUMV_USL] AS [SUMV_USL]
,(SELECT TOP 1 v021.[IDSPEC] FROM [IES].[T_V021_MED_SPEC] v021 WHERE v021.[CODE_SPEC]=usl.[PRVS] AND v021.[DATEBEG]<=zSL.[DATE_Z_2] AND (v021.[DATEEND] IS NULL OR v021.[DATEEND]>=zSL.[DATE_Z_2])) AS [PRVS3]
,usl.[COMENTU] AS [COMENTU]
FROM tempdb..TEMP_USL_MTR usl
JOIN tempdb..TEMP_SLUCH_MTR sl ON (usl.[SL_ID] = sl.[SL_ID] and usl.[IDCASE] = sl.[IDCASE])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (zSL.[IDCASE] = sl.[IDCASE])

SELECT '[IES].[T_SCHET_SLUCH_SANK]' as tableName, ' информации об удержаниях' as operationName, (select count(*) from tempdb..TEMP_SANK_MTR) as tableCount

SELECT 
 sank.SchetSluchSankID AS [SchetSluchSankID]
,zSL.[SchetSluchAccomplishedID] AS [SchetSluchAccomplished]
,sank.[S_CODE] AS [S_CODE]
,sank.[S_SUM] AS [S_SUM]
,sank.[S_TIP] AS [S_TIP]
,(select top 1 f014.[F014DenyReasonID] from [IES].[T_F014_DENY_REASON] f014 where f014.[kod] = sank.[S_OSN] and f014.DATEBEG<=isnull(sank.DATE_ACT,zSL.[DATE_Z_2]) and (f014.[DATEEND] is null or f014.[DATEEND] >= isnull(sank.DATE_ACT,zSL.[DATE_Z_2]))) AS [S_OSN]
,sank.[S_COM] AS [S_COM]
,sank.[S_IST] AS [S_IST]
,sank.[DATE_ACT] AS [DATE_ACT]
,sank.[NUM_ACT] AS [NUM_ACT]
,(select top 1 f006.[F006ControlTypeID] from [IES].[T_F006_CONTROL_TYPE] f006 where f006.[IDVID] = sank.[S_TIP] and f006.DATEBEG<=isnull(sank.DATE_ACT,zSL.[DATE_Z_2]) and (f006.[DATEEND] is null or f006.[DATEEND] >= isnull(sank.DATE_ACT,zSL.[DATE_Z_2]))) AS [S_TIP_F006]
,slid.[SchetSluchID] AS [SchetSluch]
FROM tempdb..TEMP_SANK_MTR sank
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (zSL.[IDCASE] = sank.[IDCASE])
outer apply(select top 1 sl.[SchetSluchID] from tempdb..TEMP_SANK_SL_ID_MTR slid JOIN tempdb..TEMP_SLUCH_MTR sl ON slid.[IDCASE] = sl.[IDCASE] and slid.[SL_ID] = sl.[SL_ID] where zSL.[IDCASE] = slid.[IDCASE] and sank.[S_CODE] = slid.[S_CODE] and sank.[S_SUM] = slid.[S_SUM] order by sl.SUM_M desc)slid

SELECT '[IES].[T_SCHET_SLUCH_SANK_EXP]' as tableName, ' информации об экспертах' as operationName, (select count(*) from tempdb..TEMP_CODE_EXP_MTR) as tableCount

SELECT 
 NEWID() AS [SchetSluchSankExpID]
,sank.SchetSluchSankID AS [SchetSluchSank]
,codeexp.[CODE_EXP] AS [F004Expert]
FROM tempdb..TEMP_CODE_EXP_MTR codeexp  
JOIN tempdb..TEMP_SANK_MTR sank ON (sank.[IDCASE] = codeexp.[IDCASE] and sank.[S_CODE] = codeexp.[S_CODE])

END

END

DROP TABLE #Errors

DROP TABLE tempdb..TEMP_SCHET_MTR
DROP TABLE tempdb..TEMP_ZGLV_MTR
DROP TABLE tempdb..TEMP_ZAP_MTR
DROP TABLE tempdb..TEMP_PACIENT_MTR
DROP TABLE tempdb..TEMP_Z_SLUCH_MTR
DROP TABLE tempdb..TEMP_SLUCH_MTR
DROP TABLE tempdb..TEMP_OS_SLUCH_MTR
DROP TABLE tempdb..TEMP_USL_MTR 
DROP TABLE tempdb..TEMP_SANK_MTR
DROP TABLE tempdb..TEMP_SANK_SL_ID_MTR
DROP TABLE tempdb..TEMP_CODE_EXP_MTR
DROP TABLE tempdb..TEMP_KSG_KPG_MTR
DROP TABLE tempdb..TEMP_CRIT_MTR
DROP TABLE tempdb..TEMP_SL_KOEF_MTR 
DROP TABLE tempdb..TEMP_DS2_N_MTR 
DROP TABLE tempdb..TEMP_DS2_MTR 
DROP TABLE tempdb..TEMP_DS3_MTR 
DROP TABLE tempdb..TEMP_CONS_MTR 
DROP TABLE tempdb..TEMP_NAPR_MTR
DROP TABLE tempdb..TEMP_ONK_SL_MTR 
DROP TABLE tempdb..TEMP_B_DIAG_MTR 
DROP TABLE tempdb..TEMP_B_PROT_MTR
DROP TABLE tempdb..TEMP_ONK_USL_MTR
DROP TABLE tempdb..TEMP_LEK_PR_DATE_INJ_MTR

END