业务流程待办分析

1、获取明细数据
select tb.user_name as 待办人,tb.dept_name as 待办人当前部门, ta.*
from
(select
tt.bd_title as 文件标题,tt.deptname as 起草部门,tt.pername as 起草人,
to_char(tt.create_date,'yyyy-mm-dd') as 起草时间,
to_char(t.SART_DATETIME,'yyyy-mm-dd') as 待办时间,t.WORKFLOW_NAME,
substr(t.RECEIVER_DN,instr(t.RECEIVER_DN,'=',1,1)+1,instr(t.RECEIVER_DN,',',1,1)-instr(t.RECEIVER_DN,'=',1,1)-1) as userid,
tt.bd_id as 表单ID,
t.workflow_guid,t.workflow_id
from cordys.task_list_wait t,oa_ywlc.biaodan tt ,o_dn t1 
where t.WORKFLOW_MODULE != '公文模块'
and substr(tt.deptdn,1,instr(tt.deptdn,'@',1,1)-1)= t1.o_dn
and tt.bd_id = t.BO_ID
and t.SART_DATETIME <= to_date('20120601','yyyymmdd')

) ta,ldap_exp_rawdata tb
where ta.userid = tb.g_mail(+)
order by ta.userid,ta.起草时间 desc

2、汇总统计
select substr(t.RECEIVER_DN,instr(t.RECEIVER_DN,'=',1,1)+1,instr(t.RECEIVER_DN,',',1,1)-instr(t.RECEIVER_DN,'=',1,1)-1),t1.o_name,count(*) as aa
from cordys.task_list_wait t,oa_ywlc.biaodan tt,o_dn t1
where t.WORKFLOW_MODULE != '公文模块'
and substr(tt.deptdn,1,instr(tt.deptdn,'@',1,1)-1)= t1.o_dn
and tt.bd_id = t.BO_ID
and t.SART_DATETIME <= to_date('20120601','yyyymmdd')
group by substr(t.RECEIVER_DN,instr(t.RECEIVER_DN,'=',1,1)+1,instr(t.RECEIVER_DN,',',1,1)-instr(t.RECEIVER_DN,'=',1,1)-1),t1.o_name
having count(*) > 3
order by t1.o_name,aa desc

3、按人汇总统计
select substr(t.RECEIVER_DN,instr(t.RECEIVER_DN,'=',1,1)+1,instr(t.RECEIVER_DN,',',1,1)-instr(t.RECEIVER_DN,'=',1,1)-1),tb.user_name,t1.o_name,tb.dept_name,count(*) as aa
from cordys.task_list_wait t,oa_ywlc.biaodan tt,o_dn t1,ldap_exp_rawdata tb
where t.WORKFLOW_MODULE != '公文模块'
and substr(tt.deptdn,1,instr(tt.deptdn,'@',1,1)-1)= t1.o_dn
and substr(t.RECEIVER_DN,instr(t.RECEIVER_DN,'=',1,1)+1,instr(t.RECEIVER_DN,',',1,1)-instr(t.RECEIVER_DN,'=',1,1)-1)= tb.g_mail(+)
and tt.bd_id = t.BO_ID
and t.SART_DATETIME <= to_date('20120607','yyyymmdd')
group by substr(t.RECEIVER_DN,instr(t.RECEIVER_DN,'=',1,1)+1,instr(t.RECEIVER_DN,',',1,1)-instr(t.RECEIVER_DN,'=',1,1)-1),
tb.user_name,t1.o_name,tb.dept_name
having count(*) > 3
order by t1.o_name,aa desc
其中，时间可以设置某个时间之前的，having可以去掉，少于3个的待办也可统计到。