昆明医科大学第一附属医院费用统计 (2023-04-18)

WITH base_query AS ( SELECT t.inpatient_no, t.fee_code, t.tot_cost, b.hospital_didt FROM fin_ipb_feeinfo t INNER JOIN fin_ipr_inmaininfo b ON t.inpatient_no = b.inpatient_no WHERE t.balance_state IN ('0', '1') AND t.fee_date >= TO_DATE('2023-04-18 00:00:00','YYYY-MM-DD HH24:MI:SS') AND t.fee_date <= TO_DATE('2023-04-18 10:00:00','YYYY-MM-DD HH24:MI:SS') ), aggregated_query AS ( SELECT DECODE(hospital_didt, 'YLV21051', '昆明医科大学第一附属医院', '43120189-1-2', '昆明医科大学第一附属医院呈贡医院') AS 院区, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '01' THEN tot_cost ELSE 0 END) AS 西药费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '02' THEN tot_cost ELSE 0 END) AS 中成药费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '03' THEN tot_cost ELSE 0 END) AS 中草药费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '04' THEN tot_cost ELSE 0 END) AS 挂号费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '05' THEN tot_cost ELSE 0 END) AS 床位费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '06' THEN tot_cost ELSE 0 END) AS 诊疗费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '07' THEN tot_cost ELSE 0 END) AS 检查费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '08' THEN tot_cost ELSE 0 END) AS 治疗费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '09' THEN tot_cost ELSE 0 END) AS 护理费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '10' THEN tot_cost ELSE 0 END) AS 手术费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '11' THEN tot_cost ELSE 0 END) AS 化验费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '12' THEN tot_cost ELSE 0 END) AS 放射费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '14' THEN tot_cost ELSE 0 END) AS 其他费 FROM base_query WHERE balance_state = '0' GROUP BY hospital_didt UNION ALL SELECT DECODE(hospital_didt, 'YLV21051', '昆明医科大学第一附属医院', '43120189-1-2', '昆明医科大学第一附属医院呈贡医院') AS 院区, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '01' THEN tot_cost ELSE 0 END) AS 西药费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '02' THEN tot_cost ELSE 0 END) AS 中成药费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '03' THEN tot_cost ELSE 0 END) AS 中草药费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '04' THEN tot_cost ELSE 0 END) AS 挂号费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '05' THEN tot_cost ELSE 0 END) AS 床位费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '06' THEN tot_cost ELSE 0 END) AS 诊疗费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '07' THEN tot_cost ELSE 0 END) AS 检查费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '08' THEN tot_cost ELSE 0 END) AS 治疗费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '09' THEN tot_cost ELSE 0 END) AS 护理费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '10' THEN tot_cost ELSE 0 END) AS 手术费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '11' THEN tot_cost ELSE 0 END) AS 化验费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '12' THEN tot_cost ELSE 0 END) AS 放射费, SUM(CASE WHEN fun_get_fee_stat_cate('ZY01', fee_code) = '14' THEN tot_cost ELSE 0 END) AS 其他费 FROM base_query WHERE balance_state = '1' AND balance_date > TO_DATE('2023-04-18 10:00:00', 'YYYY-MM-DD HH24:MI:SS') GROUP BY hospital_didt ) SELECT 院区, SUM(西药费) AS 西药费, SUM(中成药费) AS 中成药费, SUM(中草药费) AS 中草药费, SUM(挂号费) AS 挂号费, SUM(床位费) AS 床位费, SUM(诊疗费) AS 诊疗费, SUM(检查费) AS 检查费, SUM(治疗费) AS 治疗费, SUM(护理费) AS 护理费, SUM(手术费) AS 手术费, SUM(化验费) AS 化验费, SUM(放射费) AS 放射费, SUM(其他费) AS 其他费 FROM aggregated_query GROUP BY 院区;