
                
                with curDT as (select dbo.tzGetDate() as curDate)
                 
                
                SELECT 
                    d.DeathSvid_id as "DeathSvid_id",
                    d.Server_id as "Server_id",
                    d.DeathSvid_IsBad as "DeathSvid_IsBad",
                    COALESCE(d.DeathSvid_IsActual, 2) as "DeathSvid_IsActual",

                    d.DeathSvid_IsLose as "DeathSvid_IsLose",
                    d.DeathSvid_Ser as "DeathSvid_Ser",
                    d.DeathSvid_Num as "DeathSvid_Num",
                    to_char(d.DeathSvid_GiveDate, 'DD.MM.YYYY') as "DeathSvid_GiveDate",

                    COALESCE(rtrim(p.Person_Surname)||' ','') || COALESCE(rtrim(p.Person_Firname)||' ','') || COALESCE(rtrim(p.Person_Secname),'') as "Person_FIO",

                    COALESCE(to_char(p.Person_Birthday, 'DD.MM.YYYY'), d.DeathSvid_BirthDateStr) as "Person_Birthday",


                    dbo.Age2(p.Person_BirthDay, (select curDate from curDT)) as "Person_Age",
                    COALESCE(to_char(d.DeathSvid_DeathDate, 'DD.MM.YYYY'), d.DeathSvid_DeathDateStr) as "DeathSvid_DeathDate",
                    to_char(d.DeathSvid_signDT, 'DD.MM.YYYY') as "DeathSvid_signDT",
                    d.DeathSvid_IsSigned as "DeathSvid_IsSigned",

                    L.Lpu_Nick as "Lpu_Nick",
                    LT.LpuType_Code as "LpuType_Code",
                    d.Lpu_id as "Lpu_id",
                    COALESCE(rtrim(MP.Person_Surname)||' ','') || COALESCE(rtrim(MP.Person_Firname)||' ','') || COALESCE(rtrim(MP.Person_Secname),'') as "MedPersonal_FIO",
                    MP.MedPersonal_id as "MedPersonal_id",

                    dst.DeathSvidType_Name as "DeathSvidType_Name",
                    d.DeathSvidType_id as "DeathSvidType_id",
                    d.Person_rid as "Person_rid",
                    d.Person_id as "Person_id",
                    d.DeathSvid_IsDuplicate as "DeathSvid_IsDuplicate",
                    COALESCE(diid.Diag_Code || '. ','') || diid.Diag_Name as "Diag_iidName",

                    COALESCE(dmid.Diag_Code || '. ','') || dmid.Diag_Name as "Diag_midName",

                    COALESCE(deid.Diag_Code || '. ','') || deid.Diag_Name as "Diag_eidName",

                    COALESCE(dtid.Diag_Code || '. ','') || dtid.Diag_Name as "Diag_tidName",

                    case when PI.PersonInfo_IsParsDeath = 2 then 1 else 0 end as "PersonInfo_IsParsDeath",
                    case when PI.PersonInfo_IsSetDeath = 2 then 1 else 0 end as "PersonInfo_IsSetDeath",
                    d.ReceptType_id as "ReceptType_id",
                    DSR.DeathSvidReviewStatus_id as "DeathSvidReviewStatus_id",
                    DSRS.DeathSvidReviewStatus_Name as "DeathSvidReviewStatus_Name",
                    to_char(d.DeathSvid_insDT, 'DD.MM.YYYY') as "DeathSvid_insDT",
                    (case when COALESCE(psr.Person_SurName, dsp.DeathSvidPol_SurName) is null then ' Фамилия получателя,' ELSE '' end ||
                     case when COALESCE(psr.Person_FirName, dsp.DeathSvidPol_FirName) is null then ' Имя получателя,' ELSE '' end ||
                     case when COALESCE(dtfrmis.DocumentTypeFRMIS_id, dspdtmis.DocumentTypeFRMIS_id)	is null then ' Тип документа,' ELSE '' end ||
                     case when COALESCE(psr.Document_Ser,	dspd.Document_Ser) is null then ' Серия документа,' ELSE '' end ||
                     case when COALESCE(psr.Document_Num, dspd.Document_Num) is null then ' Номер документа,' ELSE '' end ||
                     case when COALESCE(doc.Document_begDate, dspd.Document_begDate) is null then ' Дата документа,' ELSE '' end ||
                     case when COALESCE(od.Org_id, dspo.Org_id) is null then ' Кем выдан,' ELSE '' end ||
                     case when d.DeathSvid_GiveDate is null then ' Дата выдачи МСС,' ELSE '' end ||
                     case when d.DeathSvid_RcpDate is null then ' Дата получения МСС,' ELSE '' end ||
                     case when d.DeathSvidRelation_id is null then ' Отношение к умершему,'	ELSE '' end )
                     as "MissingDataList"
                    
                    ,
                    'МСС' as "DocName",
                    to_char(d.DeathSvid_insDT, 'DD.MM.YYYY') as "DeathSvid_insDT",
                    dp.DeathPlace_Name as "DeathPlace_Name",
                    case
                         when (pa.City is null and pa.Town is null)
                         then concat_ws(', ', ua.City, ua.Town)
                         else concat_ws(', ', pa.City, pa.Town)
                    end as "CityTown",
                    case
                         when (pa.Country is null and pa.Rgn is null)
                         then concat_ws(', ', ua.Country, ua.Rgn)
                         else concat_ws(', ', pa.Country, pa.Rgn)
                    end as "CountryRgn",
                    s.Sex_Name as "Sex_Name",
                    os.Diag_oidNames as "Diag_oidNames",
                    vdst.DeathSetType_Name as "DeathSetType_Name",
                    dsc.DeathSetCause_Name as "DeathSetCause_Name"
                
                    
                FROM
                
                    v_DeathSvid d

                    left join lateral (select * from v_Diag diid where diid.Diag_id = d.Diag_iid limit 1) diid on true

                    left join lateral (select * from v_Diag dmid where dmid.Diag_id = d.Diag_mid limit 1) dmid on true

                    left join lateral (select * from v_Diag deid where deid.Diag_id = d.Diag_eid limit 1) deid on true

                    left join lateral (select * from v_Diag dtid where dtid.Diag_id = d.Diag_tid limit 1) dtid on true

                    left join v_DeathSvidPol dsp on d.DeathSvidPol_id=dsp.DeathSvidPol_id
                    left join v_Document dspd on dspd.Document_id = dsp.Document_id
                    left join v_OrgDep dspo on dspo.OrgDep_id = dspd.OrgDep_id
                    left join v_DocumentType dspdt on dspdt.DocumentType_id = dspd.DocumentType_id
                    left join v_DocumentTypeFRMIS dspdtmis on dspdt.DocumentTypeFRMIS_id = dspdtmis.DocumentTypeFRMIS_id

                    left join v_DeathSvidType dst  on dst.DeathSvidType_id = d.DeathSvidType_id

                    LEFT JOIN LATERAL (

                        select
                            p.Person_Secname,
                            p.Person_Surname,
                            p.Person_Firname,
                            p.Person_Birthday,
                            p.Sex_id,
                            p.UAddress_id,
                            p.PAddress_id
                        from v_personstate p

                        where p.Person_id = d.Person_Id
                        limit 1
                    )p ON true
                     left join v_Lpu L  on L.Lpu_id = d.Lpu_id 
                    left join v_LpuType LT  on L.LpuType_id = LT.LpuType_id

                    LEFT JOIN LATERAL(

                        select
                            PInf.PersonInfo_IsParsDeath,
                            PInf.PersonInfo_IsSetDeath
                        from v_PersonInfo PInf

                        where PInf.Person_Id = d.Person_Id
                        limit 1
                    ) PI ON true
                    LEFT JOIN LATERAL (

                        select
                            Person_Surname,
                            Person_Firname,
                            Person_Secname,
                            MedPersonal_id
                        from
                            v_MedPersonal

                        where
                            MedPersonal_id = d.MedPersonal_id
                            and Lpu_id = d.Lpu_id
                        limit 1
                    ) MP ON true
                    left join dbo.DeathSvidReview DSR on DSR.DeathSvid_id = d.DeathSvid_id
                    left join dbo.DeathSvidReviewStatus DSRS on DSRS.DeathSvidReviewStatus_id = DSR.DeathSvidReviewStatus_id
                    left join v_PersonState psr on psr.Person_id = d.Person_rid
                    left join v_Document doc on doc.Document_id = psr.Document_id
                    left join v_DocumentType dt on dt.DocumentType_id = doc.DocumentType_id
                    left join v_DocumentTypeFRMIS dtfrmis on dt.DocumentTypeFRMIS_id = dtfrmis.DocumentTypeFRMIS_id
                    left join v_OrgDep od on doc.OrgDep_id = od.OrgDep_id
                    
                    left join v_DeathPlace dp on dp.DeathPlace_id = d.DeathPlace_id
                    left join v_Sex s on s.Sex_id = p.Sex_id
                    left join v_DeathSetType vdst on vdst.DeathSetType_id = d.DeathSetType_id
                    left join v_DeathSetCause dsc on dsc.DeathSetCause_id = d.DeathSetCause_id

                    left join lateral (
                        select
                             vCity.KLCity_FullName as City,
                             vTown.KLTown_FullName as Town,
                             vRgn.KLCountry_Name as Country,
                             vRgn.KLRgn_FullName as Rgn
                        from
                             v_address vpa
                             left join v_KLCity vCity on vCity.KLCity_id = vpa.KLCity_id
                             left join v_KLTown vTown on vTown.KLTown_id = vpa.KLTown_id
                             left join v_KLRgn vRgn on vRgn.KLRgn_id = vpa.KLRgn_id
                        where
                             vpa.Address_id = p.UAddress_id
                        limit 1
                    ) as ua on true

                    left join lateral (
                        select
                             vCity.KLCity_FullName as City,
                             vTown.KLTown_FullName as Town,
                             vRgn.KLCountry_Name as Country,
                             vRgn.KLRgn_FullName as Rgn
                        from
                             v_address vpa
                             left join v_KLCity vCity on vCity.KLCity_id = vpa.KLCity_id
                             left join v_KLTown vTown on vTown.KLTown_id = vpa.KLTown_id
                             left join v_KLRgn vRgn on vRgn.KLRgn_id = vpa.KLRgn_id
                        where
                             vpa.Address_id = p.PAddress_id
                        limit 1
                    ) as pa on true

                    left join v_Diag doid on doid.Diag_id = d.Diag_oid
                    left join lateral (
                        select
                             coalesce(
                                  string_agg(vd.Diag_FullName, ', ' order by os.DeathSvidOtherState_IsMain desc),
                                  doid.Diag_FullName
                             ) as Diag_oidNames
                        from
                             dbo.v_DeathSvidOtherState os
                             left join v_Diag vd on vd.Diag_id = os.Diag_oid
                        where
                             os.DeathSvid_id = d.DeathSvid_id
                    ) as os on true
                

                    
                     WHERE 
                    (1=1)
                    
and ( d.DeathSvid_id in (
                        select distinct dsos.DeathSvid_id as DeathSvid_id
                            from dbo.DeathSvidOtherState dsos
                            join dbo.v_Diag d1 on d1.Diag_id = dsos.Diag_oid  and d1.Diag_Code >= 'C54.9' 
                            left join dbo.v_DeathSvid ds on ds.DeathSvid_id = dsos.DeathSvid_id
                            where d.DeathSvid_GiveDate >= cast('2021-10-07' as date)
                            and d.DeathSvid_GiveDate <= cast('2022-05-07' as date)
                        )
    ) and d.DeathSvid_GiveDate >= cast('2021-10-07' as date) and d.DeathSvid_GiveDate <= cast('2022-05-07' as date) AND d.Lpu_id = '13100902'
                    
                
                        ORDER BY
                            
                    d.DeathSvid_id DESC
                    
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
