2_4_1

WITH

    --- Секция параметров ---
    toStartOfMonth(toDate('2024-01-01')) AS dat1,
    toLastDayOfMonth(toDate('2024-12-01')) AS dat2,
    dateDiff('month', dat1, dat2) + 1 AS date_diff,
--- Секция параметров ---

    lpu AS (
              SELECT lpu_id,
                     lpu_short_name
              FROM dm_ap2.dct_lpu
              WHERE lpu_close_date IS NULL
),
     dept AS (
              SELECT DISTINCT ON (dept_id) dept_id AS dept_id,
                     facility_id,
                     dept_profile_med,
                     dept_name,
                     CASE WHEN  dept_name ilike '%патологии беременности%' OR
 dept_name ilike '%новорожденн%' OR
 dept_name ilike '%недоношенн%' OR
 dept_name ilike '%Акушерское отделение%' OR
 dept_name ilike '%РД%' OR
 dept_name ilike '%Перинатальный центр%' OR
 dept_name ilike '%Детск%' OR
 dept_name ilike '%Педиатри%' OR
 dept_name ilike '%Дневной стационар%' OR
 dept_name ilike '%ДC%' OR
 dept_name ilike '%Женская консультация%' OR
 dept_name ilike '%ЖК%' OR
 dept_name ilike '%КЖЗ%' OR
 dept_name ilike '%Клиника женского здоровья%' OR
 dept_name ilike '%ЦАОП%' OR
 dept_name ilike '%Центр амбулаторной онкологической помощи%' OR
 dept_name ilike '%неотложной медицинской помощи%' OR
 dept_name ilike '%неотложной%помощ%' OR
 dept_name ilike '%приёмного отделения%' OR
 dept_name ilike '%скорой медицинской помощи%' OR
 dept_name ilike '%СКП%' OR
 dept_name ilike '%Стационар кратковременного пребывания%' OR
 dept_name ilike '%Платны %' OR
 dept_name ilike '%ПМУ%' OR
 dept_name ilike '%КДО%' OR
 dept_name ilike '%Консультативно-диагностическое отделение%' OR
 dept_name ilike '%Консультативно-диагностический центр %' OR
 dept_name ilike '%Клинико-диагностическое отделени%' OR
 dept_name ilike '%Эндоскоп.центр%' OR
 dept_name ilike '%Эндоскопический центр %' OR
 dept_name ilike '%СКП%'  OR
 dept_name ilike '%Стационар кратковременного пребывания%' OR
 dept_name ilike '%ФЦ%' OR
 dept_name ilike '%ССК%' OR
 dept_name ilike '%Паллиатив%' OR
 dept_name ilike '%Отд.сестр.ухода%' THEN 0
                            ELSE 1
                            END AS kruglosutochnoe_dept
              FROM kis.dept
              WHERE dept_end_dt IS NULL
     ),
hosp_detail_cte AS (SELECT
    DISTINCT
    mov.movement_id,
    toDate(in_dt) AS  in_dt,
     mov.facility_id AS facility_id,
     mov.bed_profile
FROM kis.movement_history_u mov
LEFT JOIN dm_ap2.dct_lpu lpu ON mov.facility_id = lpu.lpu_id
LEFT JOIN dept ON mov.facility_id = dept.facility_id AND mov.dept_id = dept.dept_id::text
WHERE
  dept.kruglosutochnoe_dept = 1    AND
    ((lpu.lpu_short_name IS NOT NULL AND (lpu.lpu_open_date  <= lpu.lpu_close_date  OR lpu.lpu_close_date  IS NULL OR lpu.lpu_open_date IS NULL))
  OR
  (lpu.lpu_short_name IN ('(Оцифровка эпикризов) ГБУЗ "ГКБ им. С.П. Боткина"', 'ГБУЗ "ДГКБ св.Владимира ДЗМ"')))
  --AND patient_id IS NOT NULL
--  and toDate(in_dt)>='2024-01-01'
--  and toDate(in_dt)< '2025-01-01'
  AND toDate(in_dt) BETWEEN dat1 AND dat2
  ),
  hosp_grouped AS (
  SELECT
  facility_id,
  bed_profile,
  COUNT(movement_id) AS count_hosp
  FROM hosp_detail_cte
  GROUP BY facility_id,
  bed_profile),
beds AS (
        SELECT
            b.bed_count AS bed_count,
               l.lpu_short_name AS lpu_short_name,
               b.dept_id AS dept_id,
               b.facility_id AS facility_id,
               b.bed_profile_name AS bed_profile_name
          FROM kis.bed_fund b
          LEFT JOIN dm_ap2.dct_lpu l ON b.facility_id = l.lpu_id
          LEFT JOIN dept ON b.facility_id = dept.facility_id AND b.dept_id::NULLABLE(text) = dept.dept_id::text
          WHERE dept.kruglosutochnoe_dept = 1
              AND end_dt IS NULL
    ),
    beds_grouped AS (
        SELECT SUM(bed_count) AS cnt_beds,
        facility_id,
        bed_profile_name
        FROM beds
        GROUP BY facility_id, bed_profile_name
        ORDER BY facility_id
),
facility_stats AS ( -- Смотри внимательнее комментарии здесь - есть параметр в запросе
SELECT
CASE
    WHEN lpu.lpu_short_name ilike '%ГКБ № 13 ДЗМ%' THEN 'ЭПИКРИЗ ГБУЗ "ГКБ имени В.П. Демихова ДЗМ"'
    ELSE lpu.lpu_short_name
END AS lpu_short_name,
bc.bed_profile_name AS bed_profile_name,
ROUND(SUM(hg.count_hosp)/date_diff, 2) AS count_hosp, -- UPD: Ничего менять не надо: добавлен date_diff. !Менять знаменатель в зависимости от того, сколько месяцев мы берём в период, в данный момент для 12 месяцев!
SUM(bc.cnt_beds) AS cnt_beds,
round(SUM(hg.count_hosp)/(date_diff*SUM(bc.cnt_beds)),2) AS average_hospitalizations_on_bed -- UPD: Ничего менять не надо: добавлен date_diff. !Менять знаменатель в зависимости от того, сколько месяцев мы берём в период!
FROM beds_grouped bc
LEFT JOIN hosp_grouped hg ON hg.facility_id = bc.facility_id AND bc.bed_profile_name = hg.bed_profile
LEFT JOIN dm_ap2.dct_lpu lpu ON lpu.lpu_id = bc.facility_id
WHERE lpu.lpu_id  IN (
14932313333,
14932313379,
14932313343,
14932313317,
14932313311,
10394853,
14932313409,
14932313417,
14932313407,
14932313289,
14932313323,
14932313349,
14932313361,
14932313419,
14932313341,
14932313377,
14932313363,
14932313331,
14932313365,
14932313351,
14932313369)
GROUP
BY CASE
    WHEN lpu.lpu_short_name ilike '%ГКБ № 13 ДЗМ%' THEN 'ЭПИКРИЗ ГБУЗ "ГКБ имени В.П. Демихова ДЗМ"'
    ELSE lpu.lpu_short_name
END ,
bc.bed_profile_name
HAVING SUM(bc.cnt_beds) IS NOT NULL AND SUM(bc.cnt_beds) <> 0
AND SUM(hg.count_hosp) IS NOT NULL
),
moscow_average AS (
SELECT
bed_profile_name,
ROUND(AVG(average_hospitalizations_on_bed), 2) AS average_moscow
FROM facility_stats
GROUP BY bed_profile_name)
SELECT
fc.*,
ma.average_moscow
FROM facility_stats fc
LEFT JOIN moscow_average ma ON ma.bed_profile_name = fc.bed_profile_name
ORDER BY 1,2