--explain analyze
with k as (select 
   L.Lpu_Name
  , L.Lpu_OGRN
  , LB.LpuBuilding_Name
  , left(Addr.Address_Address,999) as LpuBuilding_Address
  , LUT.LpuUnitType_Name
  , LU.LpuUnit_Name
  , LS.LpuSection_id 
  , LS.LpuSection_Name
  , count (distinct B.Bed_id) as CntAll_noWard -- койки без палат из "койки по профилю"
 /* , count (distinct Pers.Person_id ) as Children --уник пациенты на койках по профилям инф детские
  , count (distinct (case when 
    (Pers.Diag_Code between 'A00' and 'A09.99')  
    then Pers.Person_id else NULL end)) as ChildrenA --уник пациенты на койках по профилям инф детские с диагн А
  , count (distinct (case when 
    (Pers.Diag_Code between 'J10' and 'J18.99')  
    then Pers.Person_id else NULL end)) as ChildrenJ --уник пациенты на койках по профилям инф детские с диагн J*/
  from v_Lpu L  --ЛПУ
    inner join dbo.v_LpuBuilding LB  on  L.Lpu_id = LB. Lpu_id --здание, структурное подразд
      and L.Lpu_begDate <= now()
      and L.Lpu_endDate is null
      and coalesce(L.Lpu_IsTest, 1) = 1
      and (LB.LpuBuilding_begDate <= now() or LB.LpuBuilding_begDate is null)
      and LB.LpuBuilding_endDate is null
    left join dbo.Address Addr  on Addr.Address_id = LB.Address_id --адрес здания
    left join dbo.v_LpuUnit LU  on LB.LpuBuilding_id = LU. LpuBuilding_id --здание, подраздление структурки
      and (LU.LpuUnit_begDate <= now() or LU.LpuUnit_begDate  is null)
      and LU.LpuUnit_endDate is null
    left join dbo.v_LpuUnitType LUT  on LU.LpuUnitType_id = LUT.LpuUnitType_id --тип структурки
    join dbo.v_LpuSection LS  on LU.LpuUnit_id = LS.LpuUnit_id
      and (LS.LpuSection_setDate <= now() or LS.LpuSection_setDate is null)
      and LS.LpuSection_disDate is null
    join v_LpuSectionBedState LSBS  on LSBS.LpuSection_id = LS.LpuSection_id --отделение
      and (LSBS.LpuSectionBedState_begDate <= now() or LSBS.LpuSectionBedState_begDate is null)
      and LSBS.LpuSectionBedState_endDate is null
    inner join fed.LpuSectionBedProfileLink LSBPL  on LSBPL.LpuSectionBedProfileLink_id = LSBS.LpuSectionBedProfileLink_fedid  --профиль койки СОЕДИНЕНИЕ ИННЕР ПОТОМУ ЧТО НУЖНЫ ТОЛЬКО ДЕТСКИЕ ИНФЕКЦ
      and (LSBPL.LpuSectionBedProfileLink_begDT <= now() or LSBPL.LpuSectionBedProfileLink_begDT is null)
      and LSBPL.LpuSectionBedProfileLink_endDT is null 
      and LSBPL.LpuSectionBedProfile_id  in (3069, 3090) --ограничение по профилю коек
     -- and LSBPL.LpuSectionBedProfile_id  in (3174, 3079, 3069) --select * from v_LpuSectionBedProfile --наименование профиля койки
    left join v_LpuSectionBedProfile LSBP  on LSBPL.LpuSectionBedProfile_id = LSBP.LpuSectionBedProfile_id --названия профилей коек
    left join v_LpuSectionWard LSW  on LS.LpuSection_id = LSW.LpuSection_id --палаты
      and LSW.LpuSectionWard_setDate <= now()
      and LSW.LpuSectionWard_disDate is null          
    left join v_LpuSectionWardLink LSWL  on LSWL.LpuSectionBedState_id = LSBS.LpuSectionBedState_id --связь палаты и отделения койки
  --  left join  Sex on Sex.Sex_id = LSW.Sex_id
    left join v_BedProperties BP   --связь коек и палаты 
      on  
      --LSW.LpuSectionWard_id = BP.LpuSectionWard_id --убирает койки не привязанные к палатам
      --and 
      LSBS.LpuSectionBedState_id = BP.LpuSectionBedState_id   
      --and BP.LpuSectionWard_id is not null --убирает койки не привязанные к палатам
      and BP.BedProperties_endDate is null
    left join v_Bed B   -- палаты
      on BP.Bed_id = B.Bed_id 
      and B.Bed_endDate is null
      
    left join lateral  --атрибуты на отделениях
        (
        select
SUM(case when (AV.Attribute_id between 267 and 268 and ASV.AttributeSign_id between 23 and 24) 
          or (AV.Attribute_id in (270,274)) 
          or (AV.Attribute_id in (269,273))
          then AV.AttributeValue_ValueInt end) as cntCOVID_Pn_Berem_AllAttr
      --  
        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 <= now() or ASV.AttributeSignValue_begDate is null)
          and (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--количество коек по атрибутам НЕ РАВНО КОЛИЧЕСТВУ КОЕК ПО КОЙКАМ (дублятся, тк коек больше , чем отделений)
  where 
    Attr.cntCOVID_Pn_Berem_AllAttr is null--  and  L.Lpu_OGRN='1025001278158'
  group by 
    L.Lpu_Name
  , L.Lpu_OGRN
  , LB.LpuBuilding_Name
  , Addr.Address_Address
  , LUT.LpuUnitType_Name
  , LU.LpuUnit_Name
  , LS.LpuSection_id 
  , LS.LpuSection_Name) 
  ,pat as (   select distinct 
        EPS.Lpu_id,
        EPS.LpuSection_id,
--        EPS.Person_id ,
	 count (distinct ps.Person_id ) as Children
      ,  count (distinct (case when 
	    (d.Diag_Code between 'A00' and 'A09.99')  
	    then ps.Person_id else NULL end)) as ChildrenA --уник пациенты на койках по профилям инф детские с диагн А
 	 , count (distinct (case when 
	    (d.Diag_Code between 'J10' and 'J18.99')  
	    then ps.Person_id else NULL end)) as ChildrenJ 
     --   D.Diag_FullName
      from v_EvnPS EPS  
        inner join v_EvnSection ES1  on ES1.EvnSection_pid = EPS.EvnPS_id 
        and EPS.LpuSection_id in (select distinct lpusection_id from k) 
          --and ES1.EvnSection_setDate <= now()
          --and (ES1.EvnSection_disDate > now() or 
          and ES1.EvnSection_disDate is null --пациент не выписан
          --and coalesce(ES1.EvnSection_IsPriem, 1) = 1 
        inner join v_PersonState as PS  on EPS.Person_id = PS.Person_id  
        and extract (year from age(EPS.EvnPS_setDate,PS.Person_BirthDay))::int<18
        inner join v_Diag as D  on D.Diag_id = coalesce(ES1.Diag_id,EPS.Diag_pid)  --диагноз
--      where-- EPS.Lpu_id = L.Lpu_id --связь с общим скриптом по ЛПУ
      --  and 
     --   EPS.LpuSection_id = k.LpuSection_id --связь с общим скриптом по Отделению
        --and dbo.AgeYMD(PS.Person_BirthDay, EPS.EvnPS_setDate,1)<18
        group by  EPS.Lpu_id,
        EPS.LpuSection_id
        )
  select k.*,coalesce(pat.children,0) children,coalesce(pat.childrena,0) childrena,coalesce(pat.childrenj,0) childrenj
  from k
  left join pat on k.lpusection_id=pat.lpusection_id
     
 /*  left join lateral --считаем пациентов
      (
        select distinct 
        EPS.Lpu_id,
        EPS.LpuSection_id,
        EPS.Person_id ,
        D.Diag_Code--,
     --   D.Diag_FullName
      from v_EvnPS EPS  
        inner join v_EvnSection ES1  on ES1.EvnSection_pid = EPS.EvnPS_id 
        and EPS.LpuSection_id = k.lpusection_id
          --and ES1.EvnSection_setDate <= now()
          --and (ES1.EvnSection_disDate > now() or 
          and ES1.EvnSection_disDate is null --пациент не выписан
          --and coalesce(ES1.EvnSection_IsPriem, 1) = 1 
        inner join v_PersonState as PS  on EPS.Person_id = PS.Person_id  
        and extract (year from age(EPS.EvnPS_setDate,PS.Person_BirthDay))::int<18
        inner join v_Diag as D  on D.Diag_id = coalesce(ES1.Diag_id,EPS.Diag_pid)  --диагноз
      where-- EPS.Lpu_id = L.Lpu_id --связь с общим скриптом по ЛПУ
      --  and 
        EPS.LpuSection_id = k.lpusection_id --связь с общим скриптом по Отделению
        --and dbo.AgeYMD(PS.Person_BirthDay, EPS.EvnPS_setDate,1)<18
      ) Pers on true
     group by 
    k.lpu_name
  , k.lpu_ogrn
  , k.lpubuilding_name
  , k.lpubuilding_address
  , k.lpuunittype_name
  , k.lpuunit_name
  , k.lpusection_id 
  , k.lpusection_name
  ,k.cntall_noward*/
