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

---流程耗时统计:归档周期
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 ) + '秒'