2023 泛微流程统计（流程耗时统计:归档周期，节点审批周期）

fu_manxing

已于 2023-12-27 13:57:40 修改

阅读量1k

点赞数 8

文章标签： sql 数据库

于 2023-12-27 13:49:02 首次发布

本文链接：https://blog.csdn.net/fu_manxing/article/details/135243869

版权

---流程耗时统计:归档周期
select LEFT(Minoperatetime,4) as mYear, LEFT(Minoperatetime,7) mYearMonth,creater,createdate,createtime,
workflow_requestbase.requestid, requestmark, requestname,status ,
Minoperatetime,Maxoperatetime,
CAST ( CAST ( DATEDIFF ( ss, Minoperatetime, Maxoperatetime ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' + CAST ( CAST ( DATEDIFF ( ss, Minoperatetime, Maxoperatetime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' + CAST ( CAST ( DATEDIFF ( ss, Minoperatetime, Maxoperatetime ) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' + CAST ( CAST ( DATEDIFF ( ss, Minoperatetime, Maxoperatetime ) % 60 AS INT ) AS VARCHAR ) + '秒' LengthOfTime

from workflow_requestbase
left join (
       select requestid,min(operatedate+' '+operatetime) as Minoperatetime,max(operatedate+' '+operatetime) as Maxoperatetime
       from workflow_requestlog

       group by requestid

) a on workflow_requestbase.requestid=a.requestid
where workflow_requestbase.currentnodetype = 3 and LEFT(Minoperatetime,4)>=2023--and workflow_requestbase.requestid = 229104

---流程耗时统计:节点审批周期
select wc.requestid as 请求id,
(select requestname
from workflow_requestbase wr
where wr.requestid = wc.requestid ) as 请求标题,

(select createdate+' '+ createtime
from workflow_requestbase wr2
where wr2.requestid = wc.requestid ) as 流程创建时间

,(select lastname from HrmResource where id = userid) as 操作者姓名

,
nodeid as 节点id,
isremark 操作类型id,
case isremark when 0 then '未操作'
when 1 then '转发'
when 2 then '已操作'
when 4 then '归档'
when 5 then '超时'
when 8 then '抄送(不需提交) '
when 9 then '抄送(需提交) '
when 'a' then '意见征询'
when 'b' then '回复'
when 'h' then '转办'
when 'j' then '转办提交'
end as 操作类型,

(select nodename
from workflow_nodebase
where id = nodeid ) as 节点名称
,userid as 操作者id,
receivedate+ ' '+receivetime as 接收时间
,operatedate+' '+ operatetime as 操作时间
,
CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' +
CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' +
CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' +
CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime ) % 60 AS INT ) AS VARCHAR ) + '秒' 操作耗时分钟

from workflow_currentoperator wc
where wc.requestid = 239102
order by receivedate+ ' '+receivetime ,

CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' +
CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' +
CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' +
CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime ) % 60 AS INT ) AS VARCHAR ) + '秒'