SELECT nvl(i.tools_model, 'ALL'),
COUNT(decode(r.maintain_no, 'MAINTAIN', 'MAINTAIN','REPAIR', 'REPAIR', null)) as "維修總次數",
COUNT(decode(r.maintain_no, 'MAINTAIN', 'MAINTAIN', null)) as "正常維修次數",
COUNT(decode(r.maintain_no, 'REPAIR', 'REPAIR', null)) as "返修次數",
case COUNT(decode(r.maintain_no, 'MAINTAIN', 'MAINTAIN','REPAIR', 'REPAIR', null))
when 0 then 0
else
COUNT(decode(r.maintain_no, 'REPAIR', 'REPAIR', null))/COUNT(decode(r.maintain_no, 'MAINTAIN', 'MAINTAIN','MAINTAIN', 'MAINTAIN', null))
end as "返修率"
FROM smtuser.feeder_maintain_recordn r, smtuser.feeder_tools_info i
WHERE r.tools_no(+) = i.tools_no
AND TO_CHAR (r.repair_date(+), 'YYYY/MM/DD') = TO_CHAR (SYSDATE, 'YYYY/MM/DD')
GROUP BY rollup(i.tools_model)
posted on 2009-10-29 15:22
Ke 閱讀(674)
評論(0) 編輯 收藏 所屬分類:
oracle