PMS指标评价SQL脚本.docVIP

一、输电专业： 输电缺陷流程启动率（%） 统计SQL with zjlsb as (select obj_id,lczt,lchjid from mw_app.mwt_ud_sbdyx_qxjl where :USEFILTER) select round(100*(select count(*) zjls from zjlsb where nvl(LCZT,否)=是 or nvl(lchjid,空)=中止 )/(select count(*) from zjlsb),2) tjz from dual 详细列表SQL select obj_id, decode(JKDL, 1, 架空, 2, 电缆) 架空或电缆, QXBH 缺陷单编号, xlmc 线路名称, dydjmc 电压等级, GT 杆塔号或电缆设备名称, FXRQ 发现日期, (select t.mc from mw_sys.mwt_pd_deps t where t.obj_id = fxbz) 发现班组, qxxzmc 缺陷性质, jsyymc 技术原因, zryymc 责任原因, QXNR 缺陷内容, LCZT 是否启动流程, SFCL 是否处理, XQRQ 消缺日期, XQBZ 消缺班组, SFGD 是否归档, yxdwmc 地市公司, (select mc from mw_sys.mwt_pd_deps where obj_id = DJBZ) 登记班组, DJSJ 登记时间, (select mc from mw_sys.mwt_pd_emps where obj_id = djr) 登记人 from mw_app.mwt_ud_sbdyx_qxjl where :USEFILTER order by fxrq 不合格数据SQL select obj_id, decode(JKDL, 1, 架空, 2, 电缆) 架空或电缆, QXBH 缺陷单编号, xlmc 线路名称, dydjmc 电压等级, GT 杆塔号或电缆设备名称, FXRQ 发现日期, (select t.mc from mw_sys.mwt_pd_deps t where t.obj_id = fxbz) 发现班组, qxxzmc 缺陷性质, jsyymc 技术原因, zryymc 责任原因, QXNR 缺陷内容, SFCL 是否处理, XQRQ 消缺日期, XQBZ 消缺班组, SFGD 是否归档, yxdwmc 地市公司, (select mc from mw_sys.mwt_pd_deps where obj_id = DJBZ) 登记班组, DJSJ 登记时间, (select mc from mw_sys.mwt_pd_emps where obj_id = djr) 登记人 from mw_app.mwt_ud_sbdyx_qxjl where nvl(LCZT,否)=否 and nvl(LCHJID,空)中止 and :USEFILTER order by fxrq 评价得分SQL select case when score0 then round(score,2) else 0 end dfz from (select :ZBTOTAL-(100-:ZBVALUE)/2*0.3 score from dual) 标注：流程启动率应达到100%，每下降2个百分点，扣除0.3分，扣完为止。 2、输电缺陷录入及时率（%） 2.1 统计 SQL with zjlsb as (select count(*) zjls from mw_app.mwt_ud_sbdyx_qxjl where djsj is not null and fxrq is not null and :USEFILTER) select round(100*(select count(*) zjls from mw_app.mwt_ud_sbdyx_qxjl where djsj is not null and fxrq is not null and :USEFILTER and TO_NUMBER(DJSJ