流程耗时报表

种在星星里的时光

已于 2023-01-12 15:30:06 修改

阅读量150

点赞数 1

文章标签： sql Powered by 金山文档

于 2023-01-11 15:42:08 首次发布

本文链接：https://blog.csdn.net/weixin_42888772/article/details/128645856

版权

select k.requestid,k.sqr,k.sqrbm,k.sqbdh,k.xmmc,k.直接leader审批耗时,k.FIN审批耗时,k.FIN退回审批耗时,k.FIN退回次数,
k.Compliance审批耗时,k.Compliance退回审批耗时,k.Compliance退回次数,k.激励项目组审批耗时,k.董事会审批耗时,k.副CFO审批耗时,
k.VAST审批耗时,k.MAC审批耗时,k.申请人准备材料耗时,(k.CRO+k.GC+k.COO+k.CFO+k.BU) as 'CET审批耗时',k.印章管理员盖章耗时,
k.申请人提交函件耗时,k.财务部提交PC耗时,k.流程总耗时,k.流程状态,k.归档日期
from (select  w.requestid,f1.sqr,f1.sqrbm,f1.sqbdh,f1.xmmc,(select sum(t.hour) from (select DISTINCT a.id, FORMAT(TIMESTAMPDIFF(SECOND ,STR_TO_DATE(concat( a.receivedate,' ', a.receivetime),'%Y-%m-%d %H:%i:%s'),STR_TO_DATE(concat( a.operatedate,' ', a.operatetime),'%Y-%m-%d %H:%i:%s'))/3600,2) as HOUR 
from workflow_currentoperator a 
inner join workflow_requestlog b on a.requestid=b.requestid and a.nodeid=b.nodeid and b.logtype in (0,2) and a.userid=b.operator and b.OPERATETIME=a.OPERATETIME
inner join formtable_main_153 f on a.requestid=f.requestid where a.nodeid=1879 and a.requestid=w.requestid)  t) as '直接leader审批耗时',
(select sum(t.hour) from (select DISTINCT a.id, FORMAT(TIMESTAMPDIFF(SECOND ,STR_TO_DATE(concat( a.receivedate,' ', a.receivetime),'%Y-%m-%d %H:%i:%s'),STR_TO_DATE(concat( a.operatedate,' ', a.operatetime),'%Y-%m-%d %H:%i:%s'))/3600,2) as HOUR 
from workflow_currentoperator a 
inner join workflow_requestlog b on a.requestid=b.requestid and a.nodeid=b.nodeid and b.logtype in (0,2) and a.userid=b.operator and b.OPERATETIME=a.OPERATETIME
inner join formtable_main_153 f on a.requestid=f.requestid where a.nodeid=1880 and a.requestid=w.requestid)  t) as 'FIN审批耗时',
(select  FORMAT(sum(TIMESTAMPDIFF(SECOND ,STR_TO_DATE(concat( a.receivedate,' ', a.receivetime),'%Y-%m-%d %H:%i:%s'),STR_TO_DATE(concat( a.operatedate,' ', a.operatetime),'%Y-%m-%d %H:%i:%s')))/3600,2) as HOUR 
from workflow_currentoperator a 
inner join workflow_requestlog b on a.requestid=b.requestid and a.nodeid=b.nodeid  and b.logtype in (3) and a.userid=b.operator and b.OPERATETIME=a.OPERATETIME
inner join formtable_main_153 f on a.requestid=f.requestid where a.nodeid=1880 and a.requestid=w.requestid)  as 'FIN退回审批耗时'  ,
(select count(a.id) as HOUR 
from workflow_currentoperator a 
inner join workflow_requestlog b on a.requestid=b.requestid and a.nodeid=b.nodeid  and b.logtype in (3) and a.userid=b.operator and b.OPERATETIME=a.OPERATETIME
inner join formtable_main_153 f on a.requestid=f.requestid where a.nodeid=1880 and a.requestid=w.requestid)  as 'FIN退回次数'  ,
(select sum(t.hour) from (select DISTINCT a.id,