说是除数为0了 应该要写case when 语句 可是我不知道怎么加进去……感觉头都晕了
select dept.SORT_ORDER,
dept.id,
dept.department_chinese_name,
sum(a.rs) rs,
sum(a.totaldays) totaldays,
sum(a.avagdays) avagdays,
sum(a.qty) qty,
sum(a.dddvalue) dddvalue,
ROUND(sum(a.dddvalue) / sum(a.totaldays) * 100, 2 ) ddd强度,
SUM(a.tk) tk,
round(sum(a.tk) / sum(a.totaldays) * 100, 2) tk强度
from (select r.dept_id dept_id,
count(r.id) rs,
sum(CASE WHEN to_date(to_char(r.leave_dept_date, 'yyyy-MM-dd hh24:mi:ss'),'yyyy-MM-dd hh24:mi:ss') -
to_date(to_char(r.first_insection_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') = 0 THEN 1 else
to_date(to_char(r.leave_dept_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') -
to_date(to_char(r.first_insection_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') end) totaldays,
round(sum(CASE WHEN to_date(to_char(r.leave_dept_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') -
to_date(to_char(r.first_insection_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') = 0 THEN 1 else
to_date(to_char(r.leave_dept_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') -
to_date(to_char(r.first_insection_date, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') end)
/ count(r.id)) avagdays,
0 qty,
0 dddvalue,
0 tk
from ipi_registration r
where r.leave_dept_date >= to_date('2018-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
and r.leave_dept_date <= to_date('2018-06-30 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
and r.s_brztbh_dm <> '70'
group by r.dept_id
union ALL ---------------
select a.dept_id,
0,
0,
0,
sum(a.qty) qty,
sum(a.dddvalue) dddvalue,
SUM(a.tk) tk
from (select h.ORDERED_DEPT_ID dept_id,
sum(d.total_qty) qty,
dd.id,
dd.ddd_value,
dd.single_dose_specification,
dd.pharmacy_dose_scale,
dd.ddd_convert_value,
dd.DDD_VALUE,
decode(dd.DDD_VALUE,0,null,(round(sum(d.total_qty) * dd.single_dose_specification *
dd.pharmacy_dose_scale * DECODE(dd.ddd_convert_value, NULL, 1, dd.ddd_convert_value)
/ dd.DDD_VALUE,2))) dddvalue,
(CASE WHEN dd.antibiotics_level = '20' THEN (decode(dd.DDD_VALUE,0,null,
(round(sum(d.total_qty) * dd.single_dose_specification * dd.pharmacy_dose_scale
* DECODE(dd.ddd_convert_value, NULL,1, dd.ddd_convert_value) / dd.DDD_VALUE, 2)))) ELSE NULL END) tk
from ipi_registration r
inner join ipc_drug_presc_h h on r.id = h.ipi_registration_id
inner join ipc_drug_presc_d d on d.drug_presc_h_id = h.id
inner join drm_dictionary dd on dd.id = d.drug_id
where r.leave_dept_date >= to_date('2018-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
and r.leave_dept_date <= to_date('2018-06-30 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
and r.s_brztbh_dm <> '70'
and dd.antibiotics_level is not null
and dd.ddd_value is not null
and dd.DRUG_COMMON_NAME not in ('复方磺胺甲恶唑片','注射用链霉素')
group by h.ORDERED_DEPT_ID,
dd.id,
dd.ddd_value,
dd.single_dose_specification,
dd.pharmacy_dose_scale,
dd.ddd_convert_value,
dd.DDD_VALUE,
dd.antibiotics_level) a
group by a.dept_id) a
inner join hra00_department dept on dept.id = a.dept_id
group by dept.SORT_ORDER,dept.id, dept.department_chinese_name
order by dept.SORT_ORDER,dept.department_chinese_name
