--1-й запрос
with prm as (
select getdate()::timestamp as reporttimestamp, --to_char('2020-09-04', 'YYYY-MM-DD')--to_char('2020-06-09', 'YYYY-MM-DD');
       getdate()::date::timestamp as reportDate,
       dateadd('minute', - 1, dateadd('day', 1, getdate()::date::timestamp)) as Date),
Pers as
(
  select
         -- L.Lpu_id,
         -- Графа 14: Кол-во переведенных на лёгкие койки через выписку
         COUNT(distinct case
                          when (AttrHosp.cntCOVID_KoikiAll >= 0 or AttrHosp.cntPnevmo_KoikiAll >= 0 or AttrHosp.cntBerem_KoikiAll > 0) and ES.EvnSection_disDT between dateadd('hour', - 24, dateadd('minute', 1, (select Date from prm))) and (select Date from prm)
 and ES.LeaveType_id > 0
         --and LT.LeaveType_SysNick in ('ksother','ksper')
 and EPSNextRecover.EvnPS_id > 0
         --and ES.EvnSection_Count-1 = ES.EvnSection_index
         then EPS.Person_id
                        end) as cntPersCOVID_onRecoverFromHeavy_WithLeave
  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 lpu_ogrn='1025001278158'
 and L.Lpu_begDate < dateadd('day', 1, (select ReportDate from prm)) and COALESCE(L.Lpu_endDate, dateadd('day', - 7, (select ReportDate from prm))) >= dateadd('day', - 7, (select ReportDate from prm)) and EPS.EvnPS_setDT <= (select Date from prm)
 and (EPS.EvnPS_disDT > dateadd('hour', - 24 * 8, (select Date from prm)) or EPS.EvnPS_disDT is null) and EPS.EvnPS_setDT >= '01.01.2020'::date
       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_setDT <= (select Date from prm)
 and (ES.EvnSection_disDT > dateadd('hour', - 24 * 8, (select Date from prm)) or ES.EvnSection_disDT is null) and COALESCE(ES.EvnSection_IsPriem, 1) = 1
       --inner join dbo.v_Diag D  on D.Diag_id = coalesce(ES.Diag_id, EPS.Diag_id, EPS.Diag_pid)
       --and (D.Diag_Code between 'J12.' and 'J18.9' or D.Diag_Code in ('U07.1','U07.2', 'Z03.8', 'Z11.5', 'Z22.8', 'Z20.8', 'B34.2', 'B33.8'))
       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 = 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 = ES.LpuSection_id and (ASV.AttributeSignValue_begDate < ES.EvnSection_setDate or ASV.AttributeSignValue_begDate is null) and (ASV.AttributeSignValue_endDate >= COALESCE(ES.EvnSection_disDate, (select ReportDate 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 --select * from Attribute
       ) AttrHosp on true -- Атрибуты на момент госпитализации/движения
       left join dbo.v_LeaveType LT on LT.LeaveType_id = ES.LeaveType_id -- select * from dbo.v_LeaveType order by LeaveType_Code
       LEFT JOIN LATERAL
       (
         select EPSPrev_Day.EvnPS_id,
                EPSPrev_Day.Lpu_id,
                ESec.LpuSection_id,
                EPSPrev_Day.EvnPS_setDT,
                EPSPrev_Day.EvnPS_setDate,
                EPSPrev_Day.EvnPS_disDT,
                EPSPrev_Day.EvnPS_disDate
         from dbo.v_EvnPS EPSPrev_Day
              INNER JOIN LATERAL
              (
                select ESec.EvnSection_id,
                       ESec.LpuSection_id,
                       ESec.EvnSection_disDate,
                       ESec.EvnSection_disDT,
                       ESec.EvnSection_setDate,
                       ESec.EvnSection_setDT
                from dbo.v_EvnSection ESec
                where ESec.EvnSection_pid = EPSPrev_Day.EvnPS_id and
                      COALESCE(ESec.EvnSection_IsPriem, 1) = 1
                      --and ESec.EvnSection_setDT between dateadd('day', -1, EPS.EvnPS_setDT) and EPS.EvnPS_setDT
                order by ESec.EvnSection_setDT desc
                limit 1
              ) ESec on true
              inner join dbo.v_AttributeSignValue ASV on ASV.AttributeSignValue_TablePKey = ESec.LpuSection_id and (ASV.AttributeSignValue_begDate < dateadd('day', 1, COALESCE(ESec.EvnSection_disDate, ESec.EvnSection_setDate)) or ASV.AttributeSignValue_begDate is null) and (ASV.AttributeSignValue_endDate >= COALESCE(ESec.EvnSection_disDate, ESec.EvnSection_setDate) or ASV.AttributeSignValue_endDate is null) and ASV.AttributeSign_id between 23 and 29
  -- select * from AttributeSign where AttributeSign_id between 23 and 29
              inner join dbo.v_AttributeValue AV on AV.AttributeSignValue_id = ASV.AttributeSignValue_id and (AttrHosp.cntRecover_KoikiAll >= 0 and AV.Attribute_id = 272 and AV.AttributeValue_ValueInt >= 0 or (AttrHosp.cntCOVID_KoikiAll >= 0 or AttrHosp.cntPnevmo_KoikiAll >= 0 or AttrHosp.cntBerem_KoikiAll > 0) and (AV.Attribute_id in (269, 273) and AV.AttributeValue_ValueInt > 0 or AV.Attribute_id in (267, 268, 270, 274) and AV.AttributeValue_ValueInt >= 0))
         where EPSPrev_Day.Person_id = EPS.Person_id and
               (ESec.EvnSection_disDT between dateadd('day', - 1, EPS.EvnPS_setDT) and
               EPS.EvnPS_setDT or
               ESec.EvnSection_disDT is null) and
               EPS.EvnPS_setDT >= EPSPrev_Day.EvnPS_setDT and
               EPSPrev_Day.EvnPS_id <> EPS.EvnPS_id
         limit 1
       ) EPSPrev_Day on true
       LEFT JOIN LATERAL
       (
         select ESPrev.Lpu_id,
                ESPrev.EvnSection_id,
                ESPrev.LpuSection_id,
                ESPrev.EvnSection_setDate,
                ESPrev.EvnSection_Count,
                ESPrev.EvnSection_index,
                ESPrev.EvnSection_disDate,
                ESPrev.EvnSection_setDT,
                ESPrev.EvnSection_disDT
         from dbo.v_EvnSection ESPrev
              inner join dbo.v_AttributeSignValue ASV on ASV.AttributeSignValue_TablePKey = ESPrev.LpuSection_id and (ASV.AttributeSignValue_begDate < dateadd('day', 1, ESPrev.EvnSection_disDate) or ASV.AttributeSignValue_begDate is null) and (ASV.AttributeSignValue_endDate >= ESPrev.EvnSection_disDate or ASV.AttributeSignValue_endDate is null) and (ASV.AttributeSign_id between 23 and 26 or ASV.AttributeSign_id between 28 and 29)
  -- select * from AttributeSign where AttributeSign_id between 23 and 29
 and COALESCE(ESPrev.EvnSection_IsPriem, 1) = 1
              inner join dbo.v_AttributeValue AV on AV.AttributeSignValue_id = ASV.AttributeSignValue_id and (AV.Attribute_id in (269, 273) and AV.AttributeValue_ValueInt > 0 or AV.Attribute_id in (267, 268, 270, 274) and AV.AttributeValue_ValueInt >= 0)
         where
               --LT.LeaveType_SysNick in ('ksother','ksper')
               ESPrev.EvnSection_pid = EPS.EvnPS_id and
               ESPrev.EvnSection_setDT < ES.EvnSection_setDT
         limit 1
       ) ESPrevCOVID on true -- same kvs, next move
       LEFT JOIN LATERAL
       (
         select EPSPrevCovid.EvnPS_id,
                EPSPrevCovid.Lpu_id,
                ESec.LpuSection_id,
                EPSPrevCovid.EvnPS_setDT,
                EPSPrevCovid.EvnPS_setDate,
                EPSPrevCovid.EvnPS_disDT,
                EPSPrevCovid.EvnPS_disDate
         from dbo.v_EvnPS EPSPrevCovid
              INNER JOIN LATERAL
              (
                select  ESec.EvnSection_id,
                       ESec.LpuSection_id,
                       ESec.EvnSection_disDate,
                       ESec.EvnSection_setDate,
                       ESec.EvnSection_disDT,
                       ESec.LeaveType_id
                from dbo.v_EvnSection ESec
                where ESec.EvnSection_pid = EPSPrevCovid.EvnPS_id and
                      COALESCE(ESec.EvnSection_IsPriem, 1) = 1
                order by ESec.EvnSection_setDT desc
                limit 1
              ) ESec on true
              inner join dbo.v_AttributeSignValue ASV on ASV.AttributeSignValue_TablePKey = ESec.LpuSection_id and (ASV.AttributeSignValue_begDate < dateadd('day', 1, ESec.EvnSection_disDate) or ASV.AttributeSignValue_begDate is null) and (ASV.AttributeSignValue_endDate >= ESec.EvnSection_disDate or ASV.AttributeSignValue_endDate is null) and (ASV.AttributeSign_id between 23 and 26 or ASV.AttributeSign_id between 28 and 29) -- select * from AttributeSign where AttributeSign_id between 23 and 29
              inner join dbo.v_AttributeValue AV on AV.AttributeSignValue_id = ASV.AttributeSignValue_id and (AV.Attribute_id in (269, 273) and AV.AttributeValue_ValueInt > 0 or AV.Attribute_id in (267, 268, 270, 274) and AV.AttributeValue_ValueInt >= 0)
              --inner join dbo.v_Diag D1  on D1.Diag_id = coalesce(ESec.Diag_id, EPSPrevCovid.Diag_id, EPSPrevCovid.Diag_pid)
              --and (D1.Diag_Code between 'J12.' and 'J18.9' or D1.Diag_Code in ('U07.1','U07.2', 'Z03.8', 'Z11.5', 'Z22.8', 'Z20.8', 'B34.2', 'B33.8'))
         where ESPrevCOVID.EvnSection_id is null and
               EPSPrevCovid.Person_id = EPS.Person_id and
               ESec.EvnSection_disDT between dateadd('hour', - 24, ES.EvnSection_setDT) and
               ES.EvnSection_setDT and
               EPS.EvnPS_setDT >= EPSPrevCovid.EvnPS_setDT and
               EPSPrevCovid.EvnPS_id <> EPS.EvnPS_id and
               ESec.LeaveType_id > 0
               --and ESec.LeaveType_id in (682, 689) -- 101 Выписан, 110 Самовольно прерванное лечение  -- select * from dbo.v_LeaveType order by leavetype_code
         limit 1
       ) EPSPrevCOVID on true -- same kvs, next move
       LEFT JOIN LATERAL
       (
         select ESNext.Lpu_id,
                ESNext.EvnSection_id,
                ESNext.LpuSection_id,
                ESNext.EvnSection_setDate,
                ESNext.EvnSection_Count,
                ESNext.EvnSection_index,
                ESNext.EvnSection_disDate,
                ESNext.EvnSection_setDT,
                ESNext.EvnSection_disDT
         from dbo.v_EvnSection ESNext
              inner join dbo.v_AttributeSignValue ASV on ASV.AttributeSignValue_TablePKey = ESNext.LpuSection_id and (ASV.AttributeSignValue_begDate < dateadd('day', 1, ESNext.EvnSection_setDate) or ASV.AttributeSignValue_begDate is null) and (ASV.AttributeSignValue_endDate >= ESNext.EvnSection_setDate or ASV.AttributeSignValue_endDate is null) and ASV.AttributeSign_id = 27 -- Количество коек для выздоравливающих пациентов
 and COALESCE(ESNext.EvnSection_IsPriem, 1) = 1
              inner join dbo.v_AttributeValue AV on AV.AttributeSignValue_id = ASV.AttributeSignValue_id and AV.Attribute_id = 272 and AV.AttributeValue_ValueInt >= 0
         where
               --LT.LeaveType_SysNick in ('ksother','ksper')
               ESNext.EvnSection_pid = EPS.EvnPS_id and
               ESNext.EvnSection_setDT between ES.EvnSection_disDT and dateadd('hour', 24, ES.EvnSection_disDT)
         limit 1
       ) ESNextRecover on true -- same kvs, next move
       LEFT JOIN LATERAL
       (
         select EPSNext.EvnPS_id,
                EPSNext.Lpu_id,
                ESec.LpuSection_id,
                EPSNext.EvnPS_setDT,
                EPSNext.EvnPS_setDate,
                EPSNext.EvnPS_disDT,
                EPSNext.EvnPS_disDate,
                ESec.EvnSection_disDT,
                ESec.EvnSection_setDT,
                ESec.EvnSection_setDate
         from dbo.v_EvnPS EPSNext
              inner join dbo.v_EvnSection ESec on ESec.EvnSection_pid = EPSNext.EvnPS_id and COALESCE(ESec.EvnSection_IsPriem, 1) = 1
              inner join dbo.v_AttributeSignValue ASV on ASV.AttributeSignValue_TablePKey = ESec.LpuSection_id and (ASV.AttributeSignValue_begDate < dateadd('day', 1, ESec.EvnSection_setDate) or ASV.AttributeSignValue_begDate is null) and (ASV.AttributeSignValue_endDate >= ESec.EvnSection_setDate or ASV.AttributeSignValue_endDate is null) and ASV.AttributeSign_id = 27 -- Количество коек для выздоравливающих пациентов
              inner join dbo.v_AttributeValue AV on AV.AttributeSignValue_id = ASV.AttributeSignValue_id and AV.Attribute_id = 272
         where ESNextRecover.EvnSection_id is null and
               EPSNext.Person_id = EPS.Person_id and
               EPSNext.EvnPS_id <> EPS.EvnPS_id and
               EPSNext.EvnPS_setDT >= EPS.EvnPS_setDT and
               ESec.EvnSection_setDT between ES.EvnSection_disDT and dateadd('hour', 24, ES.EvnSection_disDT)
         limit 1
       ) EPSNextRecover on true
  where AttrHosp.cntCOVID_KoikiAll >= 0 or
        AttrHosp.cntPnevmo_KoikiAll >= 0 or
        AttrHosp.cntBerem_KoikiAll > 0 or
        AttrHosp.cntRecover_KoikiAll >= 0
        --   group by  L.Lpu_id
)
select 0 as "0",
       -- Графа 14: Кол-во переведенных на лёгкие койки через выписку
       sum(cntPersCOVID_onRecoverFromHeavy_WithLeave) "1"
from Pers
