
                
            with mv as (
                select
                localtimestamp as dt
            )
            
                
                SELECT 
                -- TOP SL, FIXDATE 18.05.2023, JIRA PROMEDWEB-148004
        
            PR.PersonRegister_id as "PersonRegister_id",
            PR.Lpu_iid as "Lpu_iid",
            PR.MedPersonal_iid as "MedPersonal_iid",
            PR.MorbusType_id as "MorbusType_id",
            PR.EvnNotifyBase_id as "EvnNotifyBase_id",
            PS.Person_id as "Person_id",
            PS.Server_id as "Server_id",
            RTRIM(PS.Person_SurName) as "Person_Surname",
            RTRIM(PS.Person_FirName) as "Person_Firname",
            RTRIM(PS.Person_SecName) as "Person_Secname",
            coalesce(to_char(R.BSKRegistry_nextDate,'YYYY-MM-DD'),case when PR.MorbusType_id = 84 and BSKRegistry_riskGroup = 1 then to_char((dateadd('MONTH', 18, R.BSKRegistry_setDate)), 'YYYY-MM-DD')
                     when PR.MorbusType_id = 84 and BSKRegistry_riskGroup = 2 then to_char((dateadd('MONTH', 12, R.BSKRegistry_setDate)), 'YYYY-MM-DD')
                     when PR.MorbusType_id = 84 and BSKRegistry_riskGroup = 3 then to_char((dateadd('MONTH', 6, R.BSKRegistry_setDate)), 'YYYY-MM-DD')
                     when PR.MorbusType_id = 50 then to_char((dateadd('MONTH', 6, R.BSKRegistry_setDate)), 'YYYY-MM-DD')
                     when PR.MorbusType_id = 89 then to_char((dateadd('MONTH', 6, R.BSKRegistry_setDate)), 'YYYY-MM-DD')
                     when PR.MorbusType_id = 88 then to_char((dateadd('MONTH', 6, R.BSKRegistry_setDate)), 'YYYY-MM-DD')
                     end) as "BSKRegistry_setDateNext",
            to_char(PS.Person_Birthday, 'DD.MM.YYYY') as "Person_Birthday",
            dbo.Age2(PS.Person_BirthDay, (select dt from mv)) as "Person_Age",
            to_char(ps.Person_deadDT , 'DD.MM.YYYY') as "Person_deadDT",
            Lpu.Lpu_Nick as "Lpu_Nick",
            case
                when PR.MorbusType_id = 143 then (
                    select rt.RiskType_Name
                    from v_RiskType rt
                    where rt.RiskType_id = R.BSKRegistry_riskGroup
                )
                else rt.RiskType_Name end as "RiskType_Name",
            PROUT.PersonRegisterOutCause_id as "PersonRegisterOutCause_id",
            PROUT.PersonRegisterOutCause_Name as "PersonRegisterOutCause_Name",
            to_char(PR.PersonRegister_setDate, 'DD.MM.YYYY') as "PersonRegister_setDate",
            to_char(PR.PersonRegister_disDate, 'DD.MM.YYYY') as "PersonRegister_disDate",
            to_char(R.BSKRegistry_setDate, 'DD.MM.YYYY') as "BSKRegistry_setDate",
            MP.PMUser_Name as "PMUser_Name",
            MP.pmUser_id as "pmUser_id"
        
            
                FROM
                
         dbo.v_PersonState PS 
            inner join (
                select PR.*
                from
                    (
                        select
                            PR.Person_id,
                            PR.PersonRegister_id,
                            ROW_NUMBER() OVER (PARTITION BY Person_id ORDER BY PR.PersonRegister_setDate, PR.PersonRegister_id) r
                        from dbo.v_PersonRegister PR
                        where
                            PR.MorbusType_id in (88, 84, 89, 50, 110, 111, 112, 113)
                             and cast(PR.PersonRegister_setDate as date) between '2023-01-01' and '2023-07-20' 
                    ) PersonRegister
                    inner join dbo.v_PersonRegister PR on PR.PersonRegister_id = PersonRegister.PersonRegister_id
                where PersonRegister.r = 1
            ) PR on PR.Person_id = PS.Person_id
            left join dbo.v_PersonCard PC on PC.Person_id = PS.Person_id
         and PC.LpuAttachType_id = 1 
            left join dbo.v_Lpu Lpu on Lpu.Lpu_id = PC.Lpu_id
            left join dbo.v_LpuRegion LR on LR.LpuRegion_id = PC.LpuRegion_id
            left join lateral (
                select *
                from dbo.BSKRegistry r
                where (1=1)
                    and r.Person_id = PS.Person_id
                    and r.MorbusType_id in (88,84,89,50,110,111,112,113, 143)
                    
                order by r.BSKRegistry_setDate desc
                limit 1
            ) as R on true
            left join dbo.v_PersonRegisterOutCause PROUT on PROUT.PersonRegisterOutCause_id = PR.PersonRegisterOutCause_id
            left join dbo.v_Diag DP on PR.Diag_id = DP.Diag_id
            left join dbo.v_pmUserCache MP on MP.PMUser_id = R.pmUser_insID
            left join v_RiskType rt on rt.RiskType_id = PR.RiskType_id and PR.MorbusType_id is null
        
                         WHERE 
                        (1 = 1)
                        
                
                        ORDER BY
                            
                        PR.PersonRegister_setDate DESC
                    
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
