
                
            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",
            BSKLpuGospital.BSKLpuGospital_data as "Lpu_Gospital",
            BSKTLT.TLT_data as "isTLT",
            case when BSKTLT.TLT_data is not null then '' else dbo.getbsktimefortlt(PS.Person_id) end as "TimeBeforeTlt",
            case when BSKCKV.CHKV_data = 'нет' then '' else BSKCKV.CHKV_data end as "isCKV",
            dbo.getbsktimeforckvkag(PS.Person_id, 0) as "CKVduringHour",
            BSKKAG.KAG_data as "isKAG",
            dbo.getbsktimeforckvkag(PS.Person_id, 1) as "KAGduringHour",
            DP.Diag_FullName as "Diag_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",
            R.BSKRegistry_isBrowsed as "BSKRegistry_isBrowsed"
        
            
                FROM
                
         dbo.v_PersonState PS 
            left join v_PersonCard PC on PC.Person_id = PS.Person_id
         and PC.LpuAttachType_id = 1 
            left join v_Lpu Lpu on Lpu.Lpu_id = PC.Lpu_id
            left join v_LpuRegion LR on LR.LpuRegion_id = PC.LpuRegion_id
            left join lateral(
                select *
                from dbo.BSKRegistry r
                where r.Person_id = PS.Person_id
                    and r.MorbusType_id in (19)
                order by r.BSKRegistry_setDate desc
                limit 1
            ) as R on true
            inner join lateral (
                select *
                from v_PersonRegister PR
                where
                    PR.Person_id = PS.Person_id
                    and PR.MorbusType_id in (19)
                    and coalesce(R.MorbusType_id, PR.MorbusType_id) = PR.MorbusType_id
                order by PR.PersonRegister_setDate desc
                limit 1
            ) PR on true
            inner join v_PersonRegister PersonRegister on PersonRegister.PersonRegister_id = PR.PersonRegister_id
                and PersonRegister.Person_id = PS.Person_id
            left join v_PersonRegisterOutCause PROUT on PROUT.PersonRegisterOutCause_id = PR.PersonRegisterOutCause_id
            left join lateral(
                select coalesce(rd.BSKRegistryData_AnswerText,rd.BSKRegistryData_data) as BSKLpuGospital_data
                from
                    dbo.BSKRegistry r
                    inner join dbo.BSKRegistryData rd on rd.BSKRegistry_id = r.BSKRegistry_id and rd.BSKObservElement_id = 304
                where r.Person_id = PR.Person_id
                  and r.MorbusType_id = 19
                order by r.BSKRegistry_setDate desc
                limit 1
            ) as BSKLpuGospital on true
            left join lateral(
                select coalesce(to_char(rd.BSKRegistryData_AnswerDT,'yyyy-mm-dd hh24:mi:ss.mmm'),rd.BSKRegistryData_data) CHKV_data
                from
                    dbo.BSKRegistry r
                    inner join dbo.BSKRegistryData rd on rd.BSKRegistry_id = r.BSKRegistry_id and rd.BSKObservElement_id = 302 and coalesce(to_char(rd.BSKRegistryData_AnswerDT,'yyyy-mm-dd hh24:mi:ss.mmm'),rd.BSKRegistryData_data) != 'нет'
                where r.Person_id = PR.Person_id
                  and r.MorbusType_id = 19
                order by r.BSKRegistry_setDate desc
                limit 1
            ) as BSKCKV on true
            left join lateral(
                select case when length(coalesce(to_char(rd.BSKRegistryData_AnswerDT,'yyyy-mm-dd hh24:mi:ss.mmm'),rd.BSKRegistryData_data)) > 0 then 2 else 1 end TLT_data
                from
                    dbo.BSKRegistry r
                    inner join dbo.BSKRegistryData rd on rd.BSKRegistry_id = r.BSKRegistry_id and rd.BSKObservElement_id = 274
                    inner join lateral(
                        select LastR.BSKRegistry_setDate
                        from  dbo.BSKRegistry LastR
                        where LastR.Person_id = r.Person_id
                          and LastR.MorbusType_id = r.MorbusType_id
                        order by LastR.BSKRegistry_setDate desc
                        limit 1
                    ) LastR on true
                where r.Person_id = PR.Person_id
                  and r.MorbusType_id = 19
                  and r.BSKRegistry_setDate >= DATEADD('DAY', -3, LastR.BSKRegistry_setDate)
                  and coalesce(to_char(rd.BSKRegistryData_AnswerDT,'yyyy-mm-dd hh24:mi:ss.mmm'),rd.BSKRegistryData_data) != ''
                  and coalesce(to_char(rd.BSKRegistryData_AnswerDT,'yyyy-mm-dd hh24:mi:ss.mmm'),rd.BSKRegistryData_data) != 'нет'
                order by r.BSKRegistry_setDate desc
                limit 1
            ) as BSKTLT on true
            left join lateral(
                select coalesce(to_char(rd.BSKRegistryData_AnswerDT,'yyyy-mm-dd hh24:mi:ss.mmm'),rd.BSKRegistryData_data) KAG_data
                from
                    dbo.BSKRegistry r
                    inner join dbo.BSKRegistryData rd on rd.BSKRegistry_id = r.BSKRegistry_id and rd.BSKObservElement_id = 413 and coalesce(to_char(rd.BSKRegistryData_AnswerDT,'yyyy-mm-dd hh24:mi:ss.mmm'),rd.BSKRegistryData_data) != 'нет'
                where r.Person_id = PR.Person_id
                  and r.MorbusType_id = 19
                order by r.BSKRegistry_setDate desc
                limit 1
            ) as BSKKAG on true
            left join v_Diag DP on PR.Diag_id = DP.Diag_id
            left join dbo.v_pmUserCache MP on MP.PMUser_id = R.pmUser_insID
        
                         WHERE 
                        (1 = 1) and cast(R.BSKRegistry_setDate as date) between '2023-07-01' and '2023-07-27' 
                        
                
                        ORDER BY
                            
                        coalesce(R.BSKRegistry_setDate,PR.PersonRegister_setDate) DESC
                    
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
