--http://bi.mz.mosreg.ru/#edit/etl/758
with prm as (
select GETDATE()::date::timestamp as Date),
Pers as
(
  select L.Lpu_id,
         coalesce(LB.Address_id, L.PAddress_id, L.UAddress_id, - 1) as Address_id,
         COUNT(distinct case
                          when cntKoiki_rean > 0 then PS.Person_id
                        end) as cntPers_reanCOVID,
         COUNT(distinct case
                          when cntKoiki_ana > 0 then PS.Person_id
                        end) as cntPers_anaCOVID,
         COUNT(distinct PS.Person_id) as cntPers_HirCOVID --,
         -- COUNT(distinct case when cntCOVID_KoikiAll is null and cntPnevmo_KoikiAll is null and cntBerem_KoikiAll is null and cntRecover_KoikiAll is null
         --  then PS.Person_id end) as cntPers_HirClear
  from dbo.v_Lpu L
       inner join dbo.v_EvnPS EPS on EPS.Lpu_id = L.Lpu_id and COALESCE(L.Lpu_IsTest, 1) = 1 -- and L.Lpu_id = 13100875
       --and L.Lpu_begDate < dateadd('day', 1, null/*p_registry_evnps_exponkousl_2018.Date*/)
       --and COALESCE(L.Lpu_endDate, null/*p_registry_evnps_exponkousl_2018.Date*/) >= null/*p_registry_evnps_exponkousl_2018.Date*/
 and cast (EPS.EvnPS_setdate as date) >= '01.01.2020' and EPS.EvnPS_setDate <= (select Date from prm)
 and (EPS.EvnPS_disDate > (select Date from prm)
 or EPS.EvnPS_disDate is null)
       inner join dbo.v_PersonState PS on PS.Person_id = EPS.Person_id
       inner join dbo.v_EvnSection ES on ES.EvnSection_pid = EPS.EvnPS_id and ES.EvnSection_setDate <= (select Date from prm)
 and (ES.EvnSection_disDate > (select Date from prm)
 or ES.EvnSection_disDate is null) and COALESCE(ES.EvnSection_IsPriem, 1) = 1
       inner join dbo.v_LpuSection LS on LS.LpuSection_id = ES.LpuSection_id
       inner join fed.LpuSectionBedProfileLink LSBPL on LSBPL.LpuSectionBedProfileLink_id = ES.LpuSectionBedProfileLink_fedid
       left join dbo.v_LpuSectionBedProfile LSBP on LSBP.LpuSectionBedProfile_id = LSBPL.LpuSectionBedProfile_id and LSBP.LpuSectionBedProfile_Code in ('77', '216') -- select * from dbo.v_LpuSectionBedProfile order by LpuSectionBedProfile_Name
       inner join dbo.v_LpuBuilding LB on LB.LpuBuilding_id = LS.LpuBuilding_id
       inner join dbo.v_LpuUnit LU on LU.LpuUnit_id = LS.LpuUnit_id and LU.LpuUnitType_SysNick = 'stac'
       left join dbo.v_EvnReanimatPeriod ERP on ERP.EvnReanimatPeriod_rid = EPS.EvnPS_id and ERP.EvnReanimatPeriod_setDate <= (select Date from prm)
 and (ERP.EvnReanimatPeriod_disDate > (select Date from prm)
 or ERP.EvnReanimatPeriod_disDate is null)
       LEFT JOIN LATERAL
       (
         select RO.RepositoryObserv_id,
                RO.RepositoryObserv_setDT,
                RepositoryObserv_isIVL,
                RepositoryObserv_isOxygen,
                RepositoryObserv_isNeedIVL
         from dbo.v_RepositoryObserv RO
         where RO.Evn_id in (ES.EvnSection_id, ERP.EvnReanimatPeriod_id) and
               RO.RepositoryObserv_setDT < dateadd('day', 1, (select Date from prm))
         order by RO.RepositoryObserv_setDT desc
         limit 1
       ) RO on true
       LEFT JOIN LATERAL
       (
         select SUM(case
                      when AV.Attribute_id between 267 and 268 then AV.AttributeValue_ValueInt
                    end) as cntCOVID_KoikiAll,
                SUM(case
                      when AV.Attribute_id in (270, 274) then AV.AttributeValue_ValueInt
                    end) as cntPnevmo_KoikiAll,
                SUM(case
                      when AV.Attribute_id in (269, 273) then AV.AttributeValue_ValueInt
                    end) as cntBerem_KoikiAll,
                SUM(case
                      when AV.Attribute_id in (272) then AV.AttributeValue_ValueInt
                    end) as cntRecover_KoikiAll
         from dbo.v_AttributeSignValue ASV
              inner join dbo.v_AttributeValue AV on AV.AttributeSignValue_id = ASV.AttributeSignValue_id and ASV.AttributeSignValue_TablePKey = LS.LpuSection_id and (ASV.AttributeSignValue_begDate < dateadd('day', 1, (select Date from prm)) or ASV.AttributeSignValue_begDate is null) and (ASV.AttributeSignValue_endDate >= (select Date from prm)
 or ASV.AttributeSignValue_endDate is null) and ASV.AttributeSign_id between 23 and 29 -- 23 Количество коек для пациентов с Covid-19, 24 Количество реанимационных коек для пациентов с Covid-19, 25 Количество коек для беременных пациенток с COVID-19, 26 Количество пульмонологических коек для пациентов с отрицательным COVID-19 (пневмония)
 and AV.Attribute_id between 267 and 274
       ) Attr on true
       LEFT JOIN LATERAL
       (
         select SUM(case
                      when LSBP.LpuSectionBedProfile_Code::int8 = 77 then LSBS.LpuSectionBedState_Fact
                      else null
                    end) as cntKoiki_rean,
                SUM(case
                      when LSBP.LpuSectionBedProfile_Code::int8 = 216 then LSBS.LpuSectionBedState_Fact
                      else null
                    end) as cntKoiki_ana,
                SUM(LSBS.LpuSectionBedState_Fact) as cntKoiki_Hir
         from dbo.v_LpuSectionBedState LSBS
              inner join fed.LpuSectionBedProfileLink LSBPL on LSBPL.LpuSectionBedProfileLink_id = LSBS.LpuSectionBedProfileLink_fedid and LSBS.LpuSection_id = LS.LpuSection_id and LSBS.LpuSectionBedState_begDate < dateadd('day', 1, (select Date from prm)) and (LSBS.LpuSectionBedState_endDate >= (select Date from prm)
 or LSBS.LpuSectionBedState_endDate is null)
              left join dbo.v_LpuSectionBedProfile LSBP on LSBP.LpuSectionBedProfile_id = LSBPL.LpuSectionBedProfile_id and LSBP.LpuSectionBedProfile_Code in ('77', '216') -- select * from dbo.v_LpuSectionBedProfile order by LpuSectionBedProfile_Name
       ) LSBS on true
  where cntKoiki_Hir > 0 and
        (cntCOVID_KoikiAll is null and
        cntPnevmo_KoikiAll is null and
        cntBerem_KoikiAll is null and
        cntRecover_KoikiAll is null)
  group by L.Lpu_id,
           coalesce(LB.Address_id, L.PAddress_id, L.UAddress_id, - 1) --grouping sets ((L.Lpu_id, coalesce(LB.Address_id, L.PAddress_id, L.UAddress_id, -1)), (L.Lpu_id))
)
select * from Pers
