WITH protocol_procedures AS (SELECT cascade_lvl03_surgical_procedures.*
FROM cascade_lvl03_surgical_procedures
WHERE cascade_lvl03_surgical_procedures.procedure_performed_date BETWEEN '2025-04-01 00:00:00' AND '2025-05-01 23:59:59'
AND cascade_lvl03_surgical_procedures.legal_entity_id = 1453),
groups_mappers AS (SELECT dict_table3500service_mappers.*
FROM dict_table3500service_mappers
WHERE dict_table3500service_mappers.line_number != '4.7'),
main_table AS (SELECT MAX(mappers.surgery_type_title) AS surgery_name,
mappers.line_number AS line_number,
mappers.major AS major,
mappers.minor AS minor,
countIf(DISTINCT (sp.id), (sp.patient_id IS NOT NULL AND
(mappers.line_number != '7.2' OR sp.patient_age < 1) AND
((mappers.has_icd10_codes = 0 OR dtim.code != '') AND
(mappers.has_addition_icd10_codes = 0 OR dtaim.code != '')))) AS surgeries_count,
countIf(DISTINCT (sp.id), (sp.patient_id IS NOT NULL AND
(mappers.line_number != '7.2' OR sp.patient_age < 1) AND
((mappers.has_icd10_codes = 0 OR dtim.code != '') AND
(mappers.has_addition_icd10_codes = 0 OR dtaim.code != '')) AND
(CASE
WHEN mappers.line_number IN ('13.2', '13.0')
THEN mappers.line_number = '13.0' AND patient_age < 18
WHEN mappers.line_number = '12.0' THEN false
ELSE patient_age < 18 END))) AS under_17_years_count,
countIf(DISTINCT (sp.patient_id), (sp.patient_id IS NOT NULL AND
(mappers.line_number != '7.2' OR sp.patient_age < 1) AND
((mappers.has_icd10_codes = 0 OR dtim.code != '') AND
(mappers.has_addition_icd10_codes = 0 OR dtaim.code != '')) AND
(sp.discharge_disposition_code = 'death'))) AS deaths_count,
countIf(DISTINCT (sp.patient_id), (sp.patient_id IS NOT NULL AND
(mappers.line_number != '7.2' OR sp.patient_age < 1) AND
((mappers.has_icd10_codes = 0 OR dtim.code != '') AND
(mappers.has_addition_icd10_codes = 0 OR dtaim.code != '')) AND
sp.discharge_disposition_code = 'death' AND (CASE
WHEN mappers.line_number IN ('13.2', '13.0')
THEN mappers.line_number = '13.0' AND patient_age < 18
WHEN mappers.line_number = '12.0'
THEN false
ELSE patient_age < 18 END))) AS under_17_years_deaths_count,
countIf(DISTINCT (sp.patient_id), (sp.patient_id IS NOT NULL AND
(mappers.line_number != '7.2' OR sp.patient_age < 1) AND
((mappers.has_icd10_codes = 0 OR dtim.code != '') AND
(mappers.has_addition_icd10_codes = 0 OR dtaim.code != '')) AND
sp.is_village =
1)) AS villagers_count
FROM groups_mappers mappers
LEFT JOIN protocol_procedures sp ON mappers.code = sp.service_code
LEFT JOIN dict_table3500_icd10_mappers dtim
ON dtim.code = sp.icd10_code AND mappers.mapper_id = dtim.mapper_id
LEFT JOIN dict_table3500_addition_icd10_mappers dtaim
ON dtaim.code = sp.icd10_code AND mappers.mapper_id = dtaim.mapper_id
GROUP BY mappers.line_number, mappers.major, mappers.minor
ORDER BY (mappers.major, mappers.minor) ASC),
header_table AS (SELECT 'Усього операцій, у тому числі:' AS surgery_name,
'1.0' AS line_number,
1 AS major,
0 AS minor,
SUM(mt.surgeries_count) AS surgeries_count,
SUM(mt.under_17_years_count) AS under_17_years_count,
SUM(mt.deaths_count) AS deaths_count,
SUM(mt.under_17_years_deaths_count) AS under_17_years_deaths_count,
SUM(mt.villagers_count) AS villagers_count
FROM main_table mt
WHERE mt.minor = 0)
SELECT header_table.*
FROM header_table
UNION ALL
SELECT main_table.*
FROM main_table