
                    with curD as (select localtimestamp::date as curDate)
                    select
                        distinct pt.Person_id as "ID_PAC",
                        pd.persondisp_id,
                        pt.polistype_id as "POLISTYPE_ID",
                        case
                            when pt.polistype_id = 1 then 1
                            when pt.polistype_id = 3 then 2
                            when pt.polistype_id = 4 then 3
                        end as "VPOLIS",
                        case when pt.polistype_id = 1 then pt.polis_ser end as "SPOLIS",
                        case when pt.polistype_id in (3,4) then pt.polis_num else '' end as "NPOLIS",
                        pt.person_surname as "FAM",
                        pt.person_firname as "IM",
                        pt.person_secname as "OT",
                        to_char(pt.person_birthday, 'YYYY-MM-DD') as "DR",
                        case
                            when pt.sex_id = 1 then 1
                            when pt.sex_id = 2 then 2
                        end as "W",
                        pt.person_phone as "TEL",
                        l.Lpu_f003mcod as "MO",
                        os.Orgsmo_f002smocod as "SMO",
                        pd.PersonDisp_guid as "GUID",
                        pd.PersonDisp_id as "CARDNUM",
                        md.Person_Snils as "SS_VR",
                        md.person_fio as "FIO_VR",
                        d.Diag_SCode as "DS",
                        to_char(pd.PersonDisp_begDate, 'YYYY-MM-DD') as "DATE_DN_1",
                        to_char(pd.PersonDisp_endDate, 'YYYY-MM-DD') as "DATE_DN_2",
                        case
                            when pd.PersonDisp_endDate is not null then
                                case
                                    when pd.dispouttype_id = 1 then 1
                                    when pd.dispouttype_id in (2,7) then 2
                                    when pd.dispouttype_id = 3 then 3
                                    end
                            end as "STOP_DN",
                        case
                            when pd.deseasedisptype_id = 2 then 1
                            when pd.deseasedisptype_id = 1 then 2
                            end as "C_ZAB",
                        d.diag_code as "DIAG_CODE",
                        l.org_nick as "ORG_NICK",
                        coalesce(t1.SPEC_VR, t2.SPEC_VR) as "SPEC_VR"
                    from PersonCard pc
                        inner join dbo.v_lpu l on l.lpu_id = pc.lpu_id
                        inner join v_person p on pc.person_id = p.person_id
                        inner join v_PersonState pt on pt.person_id = p.person_id
                        inner join v_PersonDisp pd on pc.person_id = pd.person_id
                        left join v_Polis po on po.Polis_id = pt.Polis_id
                        left join lateral (
                            select '2023' - date_part('year', pdh.PersonDispHist_EndDate) as sort,
                                   msc.MedSpecClass_Code                                  as SPEC_VR
                            from v_PersonDispHist pdh
                                     left join v_MedStaffFactCache msfc on msfc.MedStaffFact_id = pdh.MedStaffFact_id
                                     left join v_MedSpecOms mso on mso.MedSpecOms_id = msfc.MedSpecOms_id
                                     left join fed.v_MedSpecClass msc on msc.MedSpecClass_id = mso.MedSpecClass_id
                            where pdh.PersonDisp_id = pd.PersonDisp_id and pdh.PersonDispHist_EndDate is null
                               or pdh.PersonDispHist_EndDate > (select curDate from curD)
                            order by sort asc
                            LIMIT 1
                        ) t1 on true
                        left join lateral (
                            select msc.MedSpecClass_Code as SPEC_VR
                            from v_MedStaffFactCache msfc
                                     left join v_MedSpecOms mso on mso.MedSpecOms_id = msfc.MedSpecOms_id
                                     left join fed.v_MedSpecClass msc on msc.MedSpecClass_id = mso.MedSpecClass_id
                            where msfc.medPersonal_id = pd.medpersonal_id
                            limit 1
                        ) t2 on true
                        left join lateral (
                          select PersonDispVizit_NextDate
                          from v_PersonDispVizit pdv
                          where pdv.PersonDisp_id = pd.PersonDisp_id
                          and date_part('year', PersonDispVizit_NextDate) = '2023' limit 1
                        ) vizit on true
                        left join v_OrgSMO os on os.OrgSMO_id = po.OrgSMO_id
                        inner join v_diag d on d.diag_id = pd.diag_id
                        inner join v_medpersonal md on pd.medpersonal_id = md.medpersonal_id
                        where pc.personcard_enddate is null
                              and pc.lpu_id = '908'
                              and (date_part('year', pd.PersonDisp_begDate) = '2023' or date_part('year', vizit.PersonDispVizit_NextDate) = '2023')
