select
		ROW_NUMBER() over ( partition by PS.Person_surname,PS.Person_firname,PS.Person_secname,PS.Person_birthday order by cast(EPS.EvnPS_disDt as date) desc) as num,
		UPPER(PS.Person_surname) as pat_f,
		UPPER(PS.Person_firname) as pat_i,
		UPPER(PS.Person_secname) as pat_o,
		cast(PS.Person_birthday as date) as pat_dr,
	--	PS.Polis_Ser+' '+PS.Polis_Num as polis,
		coalesce(PS.Person_Snils,'') as snils,
		D.Diag_code as mkb,
		cast(EPS.EvnPS_setDT as date) as date_hosp,
		cast(EPS.EvnPS_disDt as date) as date_dis,
		cast(current_date as date)-cast(EPS.EvnPS_disDt as date) as ddif,
		Lpu.lpu_name as lpu,
		Lpu.Lpu_Ogrn as ogrn,
		DiagSop.DiagSop as mkb_sop,
		pht.PrehospType_Name as type_hosp
from v_Lpu as Lpu 
inner join v_EvnPS as EPS on Lpu.Lpu_id =EPS.lpu_id
inner join v_LeaveType as LT   on LT.LeaveType_id = EPS.LeaveType_id
inner join v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id 	and ES.EvnSection_Count-1 = ES.EvnSection_index	and EPS.PrehospWaifRefuseCause_id is null--!!!!!!!
inner join v_lpusection as lpusection   on lpusection.lpusection_id =ES.lpusection_id
inner join v_PersonState as PS   on EPS.Person_id = PS.Person_id
inner join lateral
(
select diag_code from v_Diag as D where D.Diag_id = ES.Diag_id
union
select diag_code from v_Diag as D where D.Diag_id = EPS.Diag_pid and ES.Diag_id is null
) d on true
left join v_EvnOtherLpu as EOL   on EOL.EvnOtherLpu_pid = ES.EvnSection_id
left join v_Lpu as lpuLeave  on lpuLeave.Lpu_id =EOL.lpu_oid
left join v_EvnLeave as EL   on EL.EvnLeave_pid = ES.EvnSection_id
left join v_EvnDie as ED   on ED.EvnDie_pid = ES.EvnSection_id 
left join v_EvnOtherStac as EOS   on EOS.EvnOtherStac_pid = ES.EvnSection_id
left join v_EvnOtherSectionBedProfile as EOSBP   on EOSBP.EvnOtherSectionBedProfile_pid = ES.EvnSection_id
--left join ResultDesease   on ResultDesease.ResultDesease_id = COALESCE(EL.ResultDesease_id, ED.ResultDesease_id, EOL.ResultDesease_id,EOS.ResultDesease_id)
--left join LeaveCause    on LeaveCause.LeaveCause_id = COALESCE(EL.LeaveCause_id, EOL.LeaveCause_id, EOS.LeaveCause_id)
/*
left join lateral 
( select from DiagSetPhase as DiagSetPhaseLeave where ES.DiagSetPhase_aid = DiagSetPhaseLeave.DiagSetPhase_id
union
select from DiagSetPhase as DiagSetPhaseLeave where EPS.DiagSetPhase_aid = DiagSetPhaseLeave.DiagSetPhase_id and ES.DiagSetPhase_aid is null
)DiagSetPhaseLeave on true
*/
left join Prehosptype as pht  on EPS.PrehospType_id = pht.PrehospType_id --экстренно или планово

LEFT JOIN LATERAL (---сопутсвующие диагнозы
        Select (
            select string_agg(d.Diag_FullName , ', ')
            from v_EvnDiagPS DiagPS
      inner join v_Diag d on DiagPS.Diag_id = d.Diag_id 
            where DiagPS.EvnDiagPS_pid = es.EvnSection_id
              and DiagPS.DiagSetClass_id = 3
        ) as DiagSop
    ) as DiagSop on true
where 1=1
and coalesce(Lpu.Lpu_IsTest, 1) = 1 
and cast(EPS.EvnPS_setDT as date) > '2021-11-20' 
and (D.diag_code  like 'I50%' or DiagSop.DiagSop like 'I50%' )
and lt.LeaveType_Code in (101,107,108,110)--согласно ТЗ
