            with cte as (
                select
                	dbo.tzGetDate() as curDt,
                	cast(dbo.tzGetDate() as date) as curDate,
                    dateadd('day', -2, CAST(cast(dbo.tzGetDate() as varchar(10))||' 20:00:00' as timestamp)) as  startDt,
              		dateadd('day', -1, CAST(cast(dbo.tzGetDate() as varchar(10))||' 19:59:59' as timestamp)) as endDt,
                    dateadd('day', -1, CAST(cast(dbo.tzGetDate() as varchar(10))||' 20:00:00' as timestamp)) as startDtNext,
                    CAST(cast(dbo.tzGetDate() as varchar(10))||' 19:59:59' as timestamp) as endDtNext
            ), t as (
                select
                    L.Lpu_id as "Lpu_id"
                    ,dsl.DataStorage_Value as "LOGIN"
                    ,dsp.DataStorage_Value as "PASSWORD"
                    ,LS.LpuSection_id as id
                    ,'LpuSection' as object
                    ,to_char((select curDt from cte), 'YYYY-MM-DD') as "DATE_S"
                    ,L.Lpu_f003mcod as "CODE_MO_R"
                    ,COALESCE(LpuBuildingCode.Value, cast(LB.LpuBuilding_Code as integer)) as "KODLPU_R"
                    ,COALESCE(fLSBP.LpuSectionBedProfile_Code, '0') as "PROFIL"
                    ,LSP.LpuSectionProfile_Code as "PODR"
                    ,PAC_VSEGO.cnt as "PAC_VSEGO"
                    ,PAC_IN.cnt as "PAC_IN"
                    ,PAC_OUT.cnt as "PAC_OUT"
                    ,0 as "NUM_GOSP"
                    ,case
                        when (COALESCE(LSBS.LpuSectionBedState_Fact, 0) - COALESCE(busyCount.busy, 0))>0
                            then COALESCE(LSBS.LpuSectionBedState_Fact, 0) - COALESCE(busyCount.busy, 0)
                            else 0
                        end
                    as "NUM_K"
                    ,case
                        when (COALESCE(LSBS.LpuSectionBedState_MaleFact, 0) - busyCount.busyMale)>0
                            then COALESCE(LSBS.LpuSectionBedState_MaleFact, 0) - busyCount.busyMale
                            else 0
                        end
                    as "NUM_K_M"
                    ,case
                        when (COALESCE(LSBS.LpuSectionBedState_FemaleFact, 0) - busyCount.busyFemale)>0
                            then COALESCE(LSBS.LpuSectionBedState_FemaleFact, 0) - busyCount.busyFemale
                            else 0
                        end
                    as "NUM_K_W"
                    ,case
                        when (COALESCE(LSBS.LpuSectionBedState_ChildFact, 0) - busyCount.busyChild)>0
                            then COALESCE(LSBS.LpuSectionBedState_ChildFact, 0) - busyCount.busyChild
                            else 0
                        end
                    as "NUM_K_C"
                from
                    v_LpuSection LS
                    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_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 LSBP.*
                        from fed.v_LpuSectionBedProfileLink LSBPL
                        inner join fed.v_LpuSectionBedProfile LSBP  on LSBP.LpuSectionBedProfile_id = LSBPL.LpuSectionBedProfile_fedid
                        where LSBPL.LpuSectionBedProfile_id = LS.LpuSectionBedProfile_id
                        limit 1
                    ) fLSBP 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 <= (select endDt from cte)
                            and COALESCE(ES.EvnSection_disDate, '2099-12-31') >= (select endDt from cte)
                    ) PAC_VSEGO 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 >= (select startDt from cte)
                            and ES.EvnSection_setDate <= (select endDt from cte)
                    ) PAC_IN 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 >= (select startDt from cte)
                            and ES.EvnSection_disDate <= (select endDt from cte)
                    ) PAC_OUT on true
                    --LEFT JOIN LATERAL (
                    --    select
                    --        COUNT(ED.EvnDirection_id) cnt,
                    --        COALESCE(sum(case when ps.Sex_id = 1 and dbo.Age2(ps.Person_BirthDay, (select curDt from cte)) >= 18 then 1 else 0 end), 0) as cntMale,
                    --        COALESCE(sum(case when ps.Sex_id = 2 and dbo.Age2(ps.Person_BirthDay, (select curDt from cte)) >= 18 then 1 else 0 end), 0) as cntFemale,
                    --        COALESCE(sum(case when dbo.Age2(ps.Person_BirthDay, (select curDt from cte)) < 18 then 1 else 0 end), 0) as cntChild
                    --    from v_EvnDirection ED
                    --    inner join v_TimetableStac_lite tts  on tts.EvnDirection_id = ed.EvnDirection_id
                    --    inner join v_PersonState ps  on ps.Person_id = ED.Person_id
                    --    where
                    --        ED.LpuSection_did = LS.LpuSection_id
                    --        and tts.TimetableStac_setDate >= (select startDtNext from cte)
                    --        and tts.TimetableStac_setDate <= (select endDtNext from cte)
                    --) NUM_GOSP on true
                    LEFT JOIN LATERAL (
                        select
                            sum(LSBS.LpuSectionBedState_Fact) LpuSectionBedState_Fact,
                            sum(case when COALESCE(LSBP.LpuSectionBedProfile_IsChild, 1) != 2 then LSBS.LpuSectionBedState_MaleFact else 0 end) LpuSectionBedState_MaleFact,
                            sum(case when COALESCE(LSBP.LpuSectionBedProfile_IsChild, 1) != 2 then LSBS.LpuSectionBedState_FemaleFact else 0 end) LpuSectionBedState_FemaleFact,
                            sum(case when COALESCE(LSBP.LpuSectionBedProfile_IsChild, 1) = 2 then LSBS.LpuSectionBedState_Fact else 0 end) LpuSectionBedState_ChildFact
                        from v_LpuSectionBedState LSBS
                        inner join v_LpuSectionBedProfile LSBP  on LSBP.LpuSectionBedProfile_id = LSBS.LpuSectionBedProfile_id
                        inner join v_LpuSection LSS  on LSS.LpuSection_id = LSBS.LpuSection_id
                        where
                            LSBS.LpuSectionBedState_id = LSBSt.LpuSectionBedState_id
                    ) LSBS on true
                    LEFT JOIN LATERAL (
                        select
                            count(*) as busy,
                            COALESCE(sum(case when ps.Sex_id = 1 and dbo.Age2(ps.Person_BirthDay, (select curDt from cte)) >= 18 then 1 else 0 end), 0) as busyMale,
                            COALESCE(sum(case when ps.Sex_id = 2 and dbo.Age2(ps.Person_BirthDay, (select curDt from cte)) >= 18 then 1 else 0 end), 0) as busyFemale,
                            COALESCE(sum(case when dbo.Age2(ps.Person_BirthDay, (select curDt from cte)) < 18 then 1 else 0 end), 0) as busyChild
                        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 <= (select endDt from cte)
                            and (ES.EvnSection_disDate > (select endDt from cte) or ES.EvnSection_disDate is null)
                    ) busyCount on true
                    inner join DataStorage dsl  on dsl.Lpu_id = LS.Lpu_id and dsl.DataStorage_Name = 'rir_login'
                    inner join DataStorage dsp  on dsp.Lpu_id = LS.Lpu_id and dsp.DataStorage_Name = 'rir_pass'
                    LEFT JOIN LATERAL (
                        select AV.AttributeValue_ValueInt as Value
                        from v_AttributeSignValue ASV
                        inner join v_AttributeSign ASign on ASign.AttributeSign_id = ASV.AttributeSign_id
                        inner join v_AttributeValue AV on AV.AttributeSignValue_id = ASV.AttributeSignValue_id
                        inner join v_Attribute A on A.Attribute_id = AV.Attribute_id
                        where
                            ASign.AttributeSign_Code = 22
                            and ASV.AttributeSignValue_TablePKey = L.Lpu_id
                            and A.Attribute_SysNick ilike 'LpuBuildingCode'
                            and (select curDate from cte) between ASV.AttributeSignValue_begDate and COALESCE(ASV.AttributeSignValue_endDate,(select curDate from cte))
                        order by
                            ASV.AttributeSignValue_begDate desc,
                            ASV.AttributeSignValue_insDT desc
                        limit 1
                    ) LpuBuildingCode On true
                where
                    LU.LpuUnitType_id in (1, 6, 7, 9)
                    and L.Lpu_f003mcod is not null
                    
            )
            select
                "Lpu_id",
                 "LOGIN",
                 "PASSWORD",
                 "id",
                 "object",
                 "DATE_S",
                 "CODE_MO_R",
                 "KODLPU_R",
                 "PROFIL",
                 MIN("PODR") as PODR,
                 "PAC_VSEGO",
                 "PAC_IN",
                 "PAC_OUT",
                 "NUM_GOSP",
                 "NUM_K",
                 "NUM_K_M",
                 "NUM_K_W",
                 "NUM_K_C"
            from t
            group by
                "Lpu_id",
                "LOGIN",
                "PASSWORD",
                "id",
                "object",
                "DATE_S",
                "CODE_MO_R",
                "KODLPU_R",
                "PROFIL",
                "PAC_VSEGO",
                "PAC_IN",
                "PAC_OUT",
                "NUM_GOSP",
                "NUM_K",
                "NUM_K_M",
                "NUM_K_W",
                "NUM_K_C"
