泛微OA 流程统计相关SQL

本篇文章介绍泛微OA 流程统计相关SQL

按月统计每月流程数

--按月统计每月流程数
--最近6个月数据统计
select TOP 6 CONVERT(VARCHAR(7),receivedate,120),COUNT(requestid) from  workflow_currentoperator
group by CONVERT(VARCHAR(7),receivedate,120)
order by CONVERT(VARCHAR(7),receivedate,120) DESC

今日流程数

--今日流程数
select count(DISTINCT(requestid)) from workflow_currentoperator
where receivedate = CONVERT(VARCHAR(10),GETDATE(),120)

按部门提取归档流程和未归档流程，按未归档流程倒叙排列

--按部门提取归档流程和未归档流程，按未归档流程倒叙排列
select TOP 4 convert(nvarchar(20), a.departmentmark) ,b.ygd,a.wgd from 

(select b.departmentmark ,count(b.departmentmark) as wgd from hrmresource a,hrmdepartment b,workflow_currentoperator c
where a.status not in (4,5,6,7)
and a.departmentid = b.id
and a.id = c.userid
and c.iscomplete = 0
group by b.departmentmark) a,
(select b.departmentmark ,count(b.departmentmark) as ygd from hrmresource a,hrmdepartment b,workflow_currentoperator c
where a.status not in (4,5,6,7)
and a.departmentid = b.id
and a.id = c.userid
and c.iscomplete = 1
group by b.departmentmark) b
where a.departmentmark = b.departmentmark
order by a.wgd desc

使用最多的流程

--使用最多的流程
select TOP 6 convert(nvarchar(20), b.workflowname),COUNT(b.workflowname)/1000.0 from workflow_currentoperator a,workflow_base b
where a.workflowid = b.id
group by b.workflowname
order by COUNT(b.workflowname) desc

最新创建的流程

--最新创建的流程
select TOP 6 convert(nvarchar(20), requestname),createdate,createtime from workflow_requestbase
order by createdate desc ,createtime desc

当天流程操作情况

--当天流程操作情况
select COUNT(a.isremark) as isremark_sl ,convert(nvarchar(20), b.isremark_name) as isremark_name from workflow_currentoperator a,isremark_dyb b
where a.receivedate = CONVERT(VARCHAR(10),GETDATE(),120) 
and a.isremark = b.isremark_id
group by a.isremark,b.isremark_name
order by a.isremark

当月流程操作情况

--当月流程操作情况
select COUNT(a.isremark) as isremark_sl ,convert(nvarchar(20), b.isremark_name) as isremark_name from workflow_currentoperator a,isremark_dyb b
where LEFT(receivedate, 7) = CONVERT(VARCHAR(7),GETDATE(),120)  
and a.isremark = b.isremark_id
group by a.isremark,b.isremark_name
order by a.isremark