create temp table covidpers on commit drop as
 
WITH
	CovidDiag as (
		SELECT * FROM dbo.v_Diag d   
		WHERE  d.Diag_SCode  IN  ('Z03.8','Z11.5','Z20.8','Z22.8','U07.1','U07.2','J12.0','J12.1','J12.2','J12.3','J12.8','J12.9',
'J13', 'J14', 'J15.0', 'J15.1', 'J15.2', 'J15.3', 'J15.4', 'J15.5', 'J15.6', 'J15.7', 'J15.8', 'J15.9', 'J16.0', 'J16.8', 'J17.0', 
'J17.1','J17.2','J17.3', 'J17.8', 'J18.0', 'J18.1', 'J18.2', 'J18.8', 'J18.9')
 
	), 
	Pers AS (
		SELECT 
			es.Person_id, 
			  case when es.EvnSection_IsPriem = 2 then 1 else 0 end  filtered
		FROM dbo.v_EvnSection es  
		INNER JOIN CovidDiag d   ON d.Diag_id = es.Diag_id
		INNER JOIN Evn   on Evn.Evn_id = es.EvnSection_pid AND Evn.Evn_setDT >= $1   
		WHERE es.EvnSection_updDT >= $2 
		GROUP BY es.Person_id  , case when es.EvnSection_IsPriem = 2 then 1 else 0 end 
		UNION
		SELECT 
			ep.Person_id, 
			  1   filtered
		FROM dbo.v_EvnVizitPL ep  
		INNER JOIN CovidDiag d   ON d.Diag_id = ep.Diag_id
		INNER JOIN Evn   on Evn.Evn_id = ep.EvnVizitPL_pid AND Evn.Evn_setDT >= $3   
		WHERE ep.EvnVizitPL_updDT >= $4
		GROUP BY ep.Person_id
		UNION
		/* Не view, так как нужно отслеживать и удаление и только по тем, кто уже в реестре*/
		SELECT Evn.Person_id, 0 filtered
		FROM dbo.EvnDiag EvnDiag  
		INNER JOIN dbo.Evn   ON Evn.Evn_id = EvnDiag.Evn_id 
		INNER JOIN CovidDiag   ON CovidDiag.Diag_id = EvnDiag.Diag_id 
		INNER JOIN dbo.CovidRegistry   ON CovidRegistry.Person_id = Evn.Person_id and CovidRegistry.CovidRegistry_RegNum is not null
		WHERE Evn.Evn_updDT >= $5 and Evn.Evn_setDT >= $6
		GROUP BY Evn.Person_id
		UNION
		SELECT Evn.Person_id, 0 filtered
		FROM dbo.v_RepositoryObserv ro  
		INNER JOIN dbo.Evn   on ro.Evn_pid = Evn.Evn_id 
		INNER JOIN dbo.EvnClass ec   on ec.EvnClass_id = Evn.EvnClass_id and ec.EvnClass_Code = 30		
		INNER JOIN dbo.CovidRegistry   ON CovidRegistry.Person_id = Evn.Person_id and CovidRegistry.CovidRegistry_RegNum is not null
		WHERE ro.RepositoryObserv_updDT >= $7 
		GROUP BY Evn.Person_id
		UNION
		SELECT lr.Person_id, 0 filtered
		FROM (
			SELECT 
				reg.Person_id, 
				reg.Evn_id, 
				row_number() over(partition by reg.Person_id order by reg.CovidRegistry_insDT desc) rn
			FROM dbo.CovidRegistry reg  
		) lr 
		LEFT JOIN dbo.v_Evn Evn   on Evn.Evn_rid = lr.Evn_id  
		LEFT JOIN dbo.RepositoryObserv ro   on ro.Evn_id = Evn.Evn_id 
		LEFT JOIN dbo.v_DeathSvid ds   on ds.Person_id = lr.Person_id  
		WHERE lr.rn = 1
			and (
				(((RO.RepositoryObserv_SpO2 is not null and RO.RepositoryObserv_IsIVL is not null and RO.RepositoryObserv_IsEKMO is not null and RO.RepositoryObserv_IsAntivirus is not null)
				or (RO.KLCountry_id is not null and RO.TransportMeans_id is not null and RO.RepositoryObserv_TransportPlace is not null))
				and RO.RepositoryObserv_updDT >= $8)
				OR
				ds.DeathSvid_updDT >= $9
			)
		GROUP BY lr.Person_id
	)
SELECT DISTINCT Pers.Person_id, cast(null as varchar(50)) uin, cast(null as bigint) Evn_id, cast(null as date) begDate, cast(null as date) endDate
FROM Pers
LEFT JOIN dbo.v_CovidRegistry CovidRegistry   on CovidRegistry.Person_id = Pers.Person_id
WHERE CovidRegistry.Person_id is null and Pers.filtered = 0
GROUP BY Pers.Person_id
UNION
select DISTINCT Person_id, uin, Evn_id, begDate, endDate
from (
	select 
		reg.Person_id, 
		reg.CovidRegistry_ResponseHTTPCode,
		fo.uin,
		fo.begDate,
		fo.endDate,
		fo.Evn_id, 
		row_number() over(partition by reg.Person_id order by reg.CovidRegistry_insDT desc) rn,
		case when Pers.Person_id is not null then 1 else 0 end changed
	FROM dbo.CovidRegistry reg  
	LEFT JOIN (
		SELECT 
			reg.Person_id,
			reg.Evn_id, 
			reg.CovidRegistry_RegNum uin,
			reg.CovidRegistry_begDate begDate,
			reg.CovidRegistry_endDate endDate,
			row_number() over(partition by reg.Person_id order by reg.CovidRegistry_insDT) rn
		FROM dbo.CovidRegistry reg  
		WHERE (reg.CovidRegistry_ResponseHTTPCode in (200, 201) and nullif(reg.CovidRegistry_RegNum, 'null') is not null) or (reg.CovidRegistry_RegNum is not null and CovidRegistrySession_id = 7) 
	) fo on fo.Person_id = reg.person_id and fo.rn = 1
	LEFT JOIN Pers on Pers.Person_id = reg.Person_id 
) x where rn = 1 and coalesce(CovidRegistry_ResponseHTTPCode,0) <> 600 and (coalesce(CovidRegistry_ResponseHTTPCode, 0) not in (200, 201, 400) or changed = 1)
 
