
                
               
        with cte  as
        (
            select
                dbo.tzGetDate() as date,
                dateadd('year', 50, dbo.tzGetDate()) as bigdate
            limit 1
        )
        
            
                
                SELECT 
                
                E.Evn_id as "Evn_id",
                E.EvnClass_SysNick as "EvnClass_SysNick",
                case
                    when E.EvnClass_SysNick = 'EvnPL' then 'Амбулаторный'
                    when E.EvnClass_SysNick = 'EvnPS' then 'Стационарный'
                end as "EvnType",
                to_char(coalesce(EPL.EvnPL_setDate, EPS.EvnPS_setDate), 'DD.MM.YYYY') as "Evn_setDate",
                to_char(coalesce(EPL.EvnPL_disDate, EPS.EvnPS_disDate), 'DD.MM.YYYY') as "Evn_disDate",
                L.Lpu_id as "Lpu_id",
                L.Lpu_Nick as "Lpu_Nick",
                D.Diag_id as "Diag_id",
                D.Diag_FullName as "Diag_FullName",
                PS.Server_id as "Server_id",
                PS.Person_id as "Person_id",
                PS.Person_SurName || ' ' || PS.Person_FirName || coalesce(' ' || PS.Person_SecName, '') as "Person_Fio",
                to_char(PS.Person_BirthDay, 'DD.MM.YYYY') as "Person_BirthDay",
                LpuAttach.Lpu_id as "LpuAttach_id",
                LpuAttach.Lpu_Nick as "LpuAttach_Nick",
                coalesce(PAddress.Address_Nick, PAddress.Address_Address) as "Person_PAddress",
                MSF.Person_Fio as "MedPersonal",
                (case when PP.RiskType_did = 2 then dbo.GetPregnancyPRRisk(PR.PersonRegister_id,1) else '' end) as "PRRiskFactor",
                (case when PP.RiskType_did = 1 then 'низкий' when PP.RiskType_did = 2 then 'высокий' else '' end) as "RiskPR",
                (case when PP.RiskType_bid = 1 then 'низкий' when PP.RiskType_bid = 2 then 'средний' when PP.RiskType_bid = 3 then 'высокий' else 'низкий' end) as "Risk572N",
                (case when dbo.GetPregnancyRiskGlobal(RT.RiskType_id, PP.RiskType_did, PP.RiskType_bid) = '1' then 'низкий' when dbo.GetPregnancyRiskGlobal(RT.RiskType_id, PP.RiskType_did, PP.RiskType_bid) = '2' then 'средний' when dbo.GetPregnancyRiskGlobal(RT.RiskType_id, PP.RiskType_did, PP.RiskType_bid) = '3' then 'высокий' else '' end) as "RiskGlobal",
                dbo.GetPregnancy1130NRisk(PR.PersonRegister_id,1,0) as "RiskFactor572N",
                case when exists(
                    select *
                    from v_PersonQuarantine PQ
                    where PQ.Person_id = PS.Person_id
                    and PQ.PersonQuarantine_endDT is null
                ) then 2 else 1 end as "PersonQuarantine_IsOn"
                ,AbQ.PregnancyAbortionQuestion_id as "PregnancyAbortionQuestion_id", coalesce(EPL.EvnPL_NumCard, EPS.EvnPS_NumCard) as "Evn_NumCard", LT.LeaveType_id as "LeaveType_id", RC.ResultClass_id as "ResultClass_id", coalesce(LT.LeaveType_Name, RC.ResultClass_Name) as "EvnResult"
                
                FROM
                
                
                (select
                    max(E.EvnClass_SysNick) as EvnClass_SysNick,
                    max(E.Evn_id) as Evn_id,
                    max(E.Lpu_id) as Lpu_id,
                    E.Person_id
                    from
                        v_Evn E
                        left join v_EvnPL EPL on EPL.EvnPL_id = E.Evn_id
                        left join v_EvnPS EPS on EPS.EvnPS_id = E.Evn_id
                        left join v_Diag D on D.Diag_id = coalesce(EPL.Diag_id, EPS.Diag_id)
                        left join lateral (
                            select
                                PersonRegister_id
                            from
                                v_PersonRegister PR
                            where
                                PR.Person_id = E.Person_id
                                and
                                PR.PersonRegisterType_id = 2
                            and
                                (PR.personregister_setdate >= coalesce(EPL.EvnPL_setDate, EPS.EvnPS_setDate) and PR.PersonRegister_disDate is null or
                                    coalesce(PR.PersonRegister_disDate, (select bigdate from cte)) >= coalesce(EPL.EvnPL_setDate, EPS.EvnPS_setDate))
                            limit 1
                        ) PR1 on true
                    WHERE
                        (1 = 1)
                        and E.Lpu_id = '7'
                        and E.Evn_setDate between '2021-03-25' and '2022-03-25'
                        and E.EvnClass_id in (3, 30)
                        and exists (select 1 from v_MorbusDiag MD  where MD.MorbusType_id = 2 and MD.Diag_id = D.Diag_id limit 1)
                        and D.Diag_id not in (1944, 1946, 11071,11072,11073,11074,11075,11076,11077,11078,11079,11089,11090,11091)
                        and PR1.PersonRegister_id is null
                    Group by E.Person_id
                ) E
                left join v_PersonPregnancy PP on PP.Evn_id = E.Evn_id
                left join v_Lpu L on L.Lpu_id = E.Lpu_id
                left join v_PersonState PS on PS.Person_id = E.Person_id
                left join v_PersonCard PC on PC.Person_id = PS.Person_id and PC.LpuAttachType_id = 1
                left join v_Lpu_all LpuAttach on LpuAttach.Lpu_id = PC.Lpu_id
                left join v_Address PAddress on PAddress.Address_id = PS.PAddress_id
                left join v_EvnPL EPL on EPL.EvnPL_id = E.Evn_id
                left join v_EvnPS EPS on EPS.EvnPS_id = E.Evn_id
                left join lateral (
                    select
                        PersonRegister_id
                    from
                        v_PersonRegister PR
                    where
                        PR.Person_id = E.Person_id
                        and
                        PR.PersonRegisterType_id = 2 -- общерегиональный
                    and
                        coalesce(PR.PersonRegister_disDate, (select bigdate from cte)) >= coalesce(EPL.EvnPL_setDate, EPS.EvnPS_setDate)
                    limit 1
                ) PR on true
                left join v_PersonRegister PRR on PRR.PersonRegister_id=PR.PersonRegister_id
                left join v_RiskType RT on RT.RiskType_id = PRR.RiskType_id
                left join dbo.v_PregnancyAbortionQuestion AbQ on AbQ.Person_id = PS.Person_id and E.Evn_id = AbQ.Evn_id
                 left join v_ResultClass RC on RC.ResultClass_id = EPL.ResultClass_id left join v_LeaveType LT on LT.LeaveType_id = EPS.LeaveType_id left join v_Diag D on D.Diag_id = coalesce(EPL.Diag_id, EPS.Diag_id) left join lateral (select * from v_EvnVizitPL where EvnVizitPL_pid = EPL.EvnPL_id and EvnVizitPL_Index = EvnVizitPL_Count - 1 limit 1) EVPL on true left join lateral (select * from v_EvnSection where EvnSection_pid = EPS.EvnPS_id and EvnSection_Index = EvnSection_Count - 1 limit 1) ES on true left join v_MedStaffFact MSF on MSF.MedStaffFact_id = coalesce (EVPL.MedStaffFact_id, ES.MedStaffFact_id)
        
                 WHERE 
                ( 1 = 1)  
                
                
                        ORDER BY
                            
                coalesce(EPL.EvnPL_disDate, EPS.EvnPS_disDate) desc,
                coalesce(EPL.EvnPL_setDate, EPS.EvnPS_setDate) desc
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
