
            SELECT query_to_xml($$
                SELECT DISTINCT
                    '2023-07-18' AS "D_SVD", -- Дата, на которую предоставляются сведения
                    COALESCE(L.Lpu_f003mcod, '') AS "CODE_MO_S", -- Реестровый номер МО
                    CASE
                        WHEN LUT.LpuUnitType_Code = 2 THEN 1 -- круглосуточный стационар
                        WHEN LUT.LpuUnitType_Code IN (3,4,5) THEN 2 -- дневной стационар
                        ELSE 0
                    END AS "USL_OK_S", -- Условия оказания МП
                    COALESCE(LB.LpuBuilding_Code, '') AS "LPU_1_S", -- Подразделение стационара
                    COALESCE(LS.LpuSection_Code, '') AS "PODR_S", -- Код отделения стационара
                    COALESCE(LS.LpuSection_Code, '') AS "NOTD", -- Наименование отделения стационара  (разобраться)
                    COALESCE(LSP.LpuSectionProfile_Code, '') AS "PROFIL", -- Профиль
                    COALESCE(LSBP.LpuSectionBedProfile_Code, '') AS "PROFIL_K", -- Профиль койки
                    CASE
                        WHEN LSBP.LpuSectionBedProfile_IsChild IS NULL THEN 1
                        ELSE LSBP.LpuSectionBedProfile_IsChild
                    END AS "W_K", -- Вид койки
                    COALESCE(LSBS.LpuSectionBedState_Fact, 0) AS "KOEK", -- Количество мест для госпитализации
                    COALESCE(BEG_P.cnt, 0) AS "BEG_P", -- Состояло пациентов на начало отчетного периода
                    COALESCE(POST_P.cnt, 0) AS "POST_P", -- Поступило пациентов за отчетный период
                    COALESCE(END_P.cnt, 0) AS  "END_P", -- Выбыло пациентов за отчетный период
                    COALESCE(PLAN_P.cnt, 0) AS "PLAN_P", -- Кол-во направлений на госпитализацию на отчетный период
                    (COALESCE(LSBS.LpuSectionBedState_Fact, 0) - COALESCE(NON_HOSP.cnt, 0) - COALESCE(busyCount.busy, 0)) AS "SV_P", -- Количество свободных коек на конец отчетного период
                    CASE
                        WHEN (COALESCE(LSBS.LpuSectionBedState_MaleFact, 0) - busyCount.busyMale) > 0
                        THEN COALESCE(LSBS.LpuSectionBedState_MaleFact, 0) - busyCount.busyMale
                        ELSE 0
                    END AS "SV_P_M", -- Из них (SV_P) Мужских
                    CASE
                        WHEN (COALESCE(LSBS.LpuSectionBedState_FemaleFact, 0) - busyCount.busyFemale) > 0
                        THEN COALESCE(LSBS.LpuSectionBedState_FemaleFact, 0) - busyCount.busyFemale
                        ELSE 0
                    END AS "SV_P_W" -- Из них (SV_P) Женских
                -- Справочник ЛПУ: отделения --
                FROM v_LpuSection LS
                -- Выписка направлений	--
                INNER JOIN v_EvnDirection ed ON ed.LpuSection_did = LS.LpuSection_id
                -- Cправочник ЛПУ --
                INNER JOIN v_Lpu L ON L.Lpu_id = LS.Lpu_id
                INNER JOIN v_LpuBuilding LB ON LB.LpuBuilding_id = LS.LpuBuilding_id
                INNER JOIN v_LpuUnit LU ON LU.LpuUnit_id = LS.LpuUnit_id
                INNER JOIN v_LpuUnitType LUT ON LUT.LpuUnitType_id = LU.LpuUnitType_id
                INNER JOIN v_LpuSectionProfile LSP ON LSP.LpuSectionProfile_id = LS.LpuSectionProfile_id
                LEFT JOIN v_LpuSectionBedState LSBSt ON LSBSt.LpuSection_id = LS.LpuSection_id
                INNER JOIN v_LpuSectionBedProfile LSBP ON LSBP.LpuSectionBedProfile_id = COALESCE(LSBSt.LpuSectionBedProfile_id, LS.LpuSectionBedProfile_id)
                LEFT JOIN LATERAL (
                    SELECT COUNT(ES.EvnSection_id) cnt
                    FROM v_EvnSection ES
                    WHERE
                        ES.Lpu_id = L.Lpu_id
                        AND ES.LpuSectionBedProfile_id = LSBP.LpuSectionBedProfile_id
                        AND ES.EvnSection_setDate >= dateadd('day', -1, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                        AND ES.EvnSection_setDate <= dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                ) POST_P ON true
                LEFT JOIN LATERAL (
                    SELECT COUNT(ES.EvnSection_id) cnt
                    FROM v_EvnSection ES
                    WHERE
                        ES.Lpu_id = L.Lpu_id
                        AND ES.LpuSectionBedProfile_id = LSBP.LpuSectionBedProfile_id
                        AND ES.EvnSection_disDate >= dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                        AND ES.EvnSection_disDate <= dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                ) END_P ON true
                LEFT JOIN LATERAL (
                    SELECT
                        COUNT(vB.Bed_Id) LpuSectionBedState_Fact,
                        SUM(
                            case
                                when COALESCE(vLSBP.LpuSectionBedProfile_IsChild, 1) != 2 AND vS.Sex_Sysnick in ('man', 'isex') then 1
                                else 0
                            end
                        ) LpuSectionBedState_MaleFact,
                        SUM(
                            case
                                when COALESCE(vLSBP.LpuSectionBedProfile_IsChild, 1) != 2 AND vS.Sex_Sysnick in ('woman') then 1
                                else 0
                            end
                        ) LpuSectionBedState_FemaleFact
                    FROM v_Bed vB
                        INNER JOIN v_BedProperties vBP on vBP.Bed_Id = vB.Bed_Id
                        INNER JOIN v_LpuSectionBedState vLSBS on vBP.LpuSectionBedState_Id = vLSBS.LpuSectionBedState_Id
                        INNER JOIN v_LpuSectionBedProfile vLSBP ON vLSBP.LpuSectionBedProfile_id = vLSBS.LpuSectionBedProfile_id
                        LEFT JOIN v_LpuSectionWard vLSW on vLSW.LpuSectionWard_Id = vBP.LpuSectionWard_Id
                        LEFT JOIN v_Sex vS on vS.Sex_Id = vLSW.Sex_Id
                        LEFT JOIN LATERAL (
                            SELECT
                                vBS.BedState_id
                            FROM
                                v_BedState vBS
                            WHERE (vBS.bed_id = vB.bed_id)
                                AND (dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp)) > vBS.BedState_BegDate)
                                AND (dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp)) < vBS.Bedstate_EndDate OR vBS.Bedstate_EndDate IS NULL)
                        ) BSB on TRUE
                    WHERE vLSBP.LpuSectionBedProfile_Id = LSBP.LpuSectionBedProfile_id
                        AND vLSBS.LpuSection_Id = LS.LpuSection_id
                        AND (vLSBS.LpuSectionBedState_BegDate IS NOT NULL AND vLSBS.LpuSectionBedState_EndDate IS NULL)
                        AND (vBP.BedProperties_BegDate IS NOT NULL AND vBP.BedProperties_EndDate IS NULL)
                        AND (
                            vB.Bed_endDate IS NULL
                            OR vB.Bed_endDate > dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                        )
                        and vB.Bed_begDate < dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                        AND (BSB.BedState_id IS NULL)
                ) LSBS ON true
                LEFT JOIN LATERAL (
                    SELECT COUNT(ES.EvnSection_id) cnt
                    FROM v_EvnSection ES
                    WHERE
                        ES.Lpu_id = L.Lpu_id
                        AND ES.LpuSectionBedProfile_id = LSBP.LpuSectionBedProfile_id
                        AND ES.EvnSection_SetDate < dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                        AND (ES.EvnSection_disDate IS NULL OR ES.EvnSection_disDate > dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp)))
                ) BEG_P ON true
                LEFT JOIN LATERAL (
                    SELECT COUNT(ED.EvnDirection_id) cnt
                    FROM v_EvnDirection ED
                    INNER JOIN v_TimetableStac_lite tts ON tts.EvnDirection_id = ed.EvnDirection_id
                    WHERE
                        ED.LpuSection_did = LS.LpuSection_id
                        AND tts.TimetableStac_setDate >= dateadd('day', -1, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                        AND tts.TimetableStac_setDate <= dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                ) PLAN_P ON true
                LEFT JOIN LATERAL(
                    SELECT
                        ES.Lpu_id,
                        COUNT(EPS.LpuSection_id) AS cnt
                    FROM dbo.v_EvnSection ES
                    INNER JOIN dbo.v_EvnPS EPS ON EPS.EvnPS_id = ES.EvnSection_pid
                    WHERE
                        EPS.Lpu_id = ES.Lpu_id
                        AND ES.Lpu_id = L.Lpu_id and ES.LpuSectionBedProfile_id = LSBP.LpuSectionBedProfile_id
                        AND EPS.LeaveType_id IS NULL
                        AND COALESCE(ES.LpuSection_id, EPS.LpuSection_pid) is NULL
                        AND ES.EvnSection_setDate <= dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                        AND (ES.EvnSection_disDate > dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp)) or ES.EvnSection_disDate is NULL)
                        GROUP by
                            ES.Lpu_id
                ) NON_HOSP ON true
                LEFT JOIN LATERAL (
                    SELECT
                        count(*) as busy,
                        COALESCE(sum(case when ps.Sex_id = 1 and dbo.Age2(ps.Person_BirthDay, cast(localtimestamp as date)) >= 18 then 1 else 0 end), 0) as busyMale,
                        COALESCE(sum(case when ps.Sex_id = 2 and dbo.Age2(ps.Person_BirthDay, cast(localtimestamp as date)) >= 18 then 1 else 0 end), 0) as busyFemale
                    FROM v_EvnSection ES
                    INNER JOIN v_PersonState ps ON ps.Person_id = ES.Person_id
                    WHERE
                        ES.Lpu_id = L.Lpu_id
                        AND ES.LpuSectionBedProfile_id = LSBP.LpuSectionBedProfile_id
                        AND ES.LpuSectionWard_id is not null
                        AND ES.EvnSection_setDate <= dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp))
                        AND (ES.EvnSection_disDate > dateadd('day', 0, cast('2023-07-18' ||' '|| '08:00' as timestamp)) or ES.EvnSection_disDate is null)
                ) busyCount ON true
                WHERE
                    L.Lpu_f003mcod IS NOT NULL
                AND LU.LpuUnitType_id IN (1, 6, 7, 9)
                AND L.Lpu_id = '13030001'
                AND LB.LpuBuilding_Code = '11204' $$, true, true, '')
        
