
                
        WITH cte AS (
            SELECT dbo.tzGetDate() AS now
        )
        
                
                SELECT 
                PP.PersonPrivilege_id as "PersonPrivilege_id"
                ,PP.Lpu_id as "Lpu_id"
                ,PS.Server_id as "Server_id"
                ,PS.PersonEvn_id as "PersonEvn_id"
                ,PS.Person_id as "Person_id"
                ,PT.PrivilegeType_id as "PrivilegeType_id"
                ,PT.PrivilegeType_Code as "PrivilegeType_Code"
                ,RTRIM(COALESCE(PT.PrivilegeType_VCode, cast(PT.PrivilegeType_Code as varchar))) as "PrivilegeType_VCode"
                ,to_char(PP.PersonPrivilege_begDate, 'dd.mm.yyyy') as "Privilege_begDate"
                ,to_char(PP.PersonPrivilege_endDate, 'dd.mm.yyyy') as "Privilege_endDate"
                ,CASE WHEN RF.ReceptFinance_Code = 1 THEN 'true' ELSE 'false' END as "Person_IsFedLgot"
                ,CASE WHEN RF.ReceptFinance_Code = 2 THEN 'true' ELSE 'false' END as "Person_IsRegLgot"
                ,CASE WHEN RF.ReceptFinance_Code = 3 THEN 'true' ELSE 'false' END as "Person_Is7Noz"
                ,CASE WHEN WDCIT.WhsDocumentCostItemType_Nick = 'orf' THEN 'true' ELSE 'false' END as "Person_IsROZ"
                ,RTRIM(PT.PrivilegeType_Name)  as "PrivilegeType_Name"
                ,RTRIM(PS.Person_SurName) as "Person_Surname"
                ,RTRIM(PS.Person_FirName) as "Person_Firname"
                ,RTRIM(PS.Person_SecName) as "Person_Secname"
                ,COALESCE(ps.Person_SurName,'')||COALESCE(' '||ps.Person_FirName,'')||COALESCE(' '||ps.Person_SecName,'') as "Person_Fio"
                ,to_char(PS.Person_Birthday, 'dd.mm.yyyy') as "Person_Birthday"
                ,
            case
                
                when datediff('day', PS.Person_Birthday, dbo.tzGetDate()) <= 31
                    then '' || datediff('day', PS.Person_Birthday, dbo.tzGetDate()) ||
                    case
                        when
                            datediff('day', PS.Person_Birthday, dbo.tzGetDate()) >= 2
                            and datediff('day', PS.Person_Birthday, dbo.tzGetDate()) <= 4
                            or
                            datediff('day', PS.Person_Birthday, dbo.tzGetDate()) >= 22
                            and datediff('day', PS.Person_Birthday, dbo.tzGetDate()) + 1 <= 24
                        then ' дня'
                        when datediff('day', PS.Person_Birthday, dbo.tzGetDate()) in (1, 21, 31)
                        then ' день'
                        else ' дней'
                    end
                when datediff('day', PS.Person_Birthday, dbo.tzGetDate()) + 1 > 31 and dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) < 12
                    then '' || dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate())::varchar || ' мес.'

                when dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) >= 12 and dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) < 36
                    then '' || dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar ||
                        case
                            when dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) = 2 then ' года '
                            else ' год '
                        end ||
                        case
                            when (dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) - (dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) * 12)) < 1 then ''
                            else (dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) - (dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) * 12))::varchar || ' мес.'
                        end

                when dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) >= 3
                    then '' || dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar ||
                        case
                    when
                        right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,2)::integer >= 11
                        and right(dbo.Age2(PS.Person_Birthday,dbo.tzGetDate())::varchar,2)::integer <= 14
                        or right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer >= 5
                        and right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer <= 9
                        or right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer = 0
                    then ' лет'
                    when
                        right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer >= 2
                        and right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer <= 4
                    then ' года'
                    when right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer = 1
                    then ' год'
                end
                    else '' || dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate())::varchar || ' мес.'
            end
            as "Person_Age"
        
                
                FROM
                
                 v_PersonState_All PS
                inner join PersonPrivilege PP on PP.Person_id = PS.Person_id
                inner join v_PrivilegeType PT on PT.PrivilegeType_id = PP.PrivilegeType_id
                inner join v_ReceptFinance RF on RF.ReceptFinance_id = PT.ReceptFinance_id
                left join WhsDocumentCostItemType WDCIT
                        on WDCIT.WhsDocumentCostItemType_id = PT.WhsDocumentCostItemType_id
                left join lateral  (
                    SELECT pd.Person_id
                    FROM
                        v_PersonDisp pd (nolock)
                        INNER JOIN v_PersonDispHist pdh ON pdh.personDisp_id = pd.personDisp_id
                    WHERE
                        (pdh.PersonDispHist_endDate IS NULL OR CAST(pdh.PersonDispHist_endDate AS timestamp) > (SELECT now FROM cte)) -- ДН еще не окончено
                        AND pdh.MedPersonal_id = '70101000003507'
                        AND pd.Person_id = PS.Person_id
                ) as PD on true
                LEFT JOIN LATERAL (
                    SELECT PersonCard_id
                    FROM
                        v_PersonCard pCard
                        LEFT JOIN v_MedStaffRegion msRegion ON msRegion.LpuRegion_id = pCard.LpuRegion_id
                        LEFT JOIN v_MedStaffFact msFact ON msFact.MedStaffFact_id = msRegion.MedStaffFact_id AND msFact.Lpu_id = msRegion.Lpu_id
                    WHERE
                        (pCard.PersonCard_endDate IS NULL OR CAST(pCard.PersonCard_endDate AS timestamp) > (SELECT now FROM cte))
                        AND msFact.MedStaffFact_id = '70101000056639'
                        AND pCard.LpuAttachType_id = 1

                        AND pCard.Person_id = PS.Person_id
                    LIMIT 1
                ) PC ON true
                 WHERE 
                (1 = 1)
                and PP.Lpu_id = '39'  and cast(PP.PersonPrivilege_endDate as date) between cast('2022-03-09' as date) and cast('2022-04-09' as date)  and PT.PrivilegeType_id not in('4875') and exists (select PP.PersonPrivilege_id from v_PersonPrivilege as PP where PP.PrivilegeType_id = PT.PrivilegeType_id and PP.Person_id = PS.Person_id  and coalesce(PP.Lpu_id, 0) not in(864866) limit 1)
                and (
                    PD.Person_id is not null
                    or
                    PC.PersonCard_id is not null
                )
                
                
                        ORDER BY
                            
                "Privilege_endDate"  DESC
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
