领导汇报_数据统计_sql.txt

1:  统计迁钢公司运行结束流程
     select count(*)  from inst_process a where state='4'
      and started>to_date('2007-02-24','yyyy-MM-dd')

2： 迁钢公司正在运行的流程
      select count(*)  from inst_process a where state='3'
      and started>to_date('2007-02-24','yyyy-MM-dd')

3:  迁钢公司结束流程,按照流程汇总查询
    
     select count(*),name  from inst_process
     where state='4'
     and started>to_date('2007-02-24','yyyy-MM-dd')
     group by name

3:  迁钢公司运行流程,按照流程汇总查询

      select count(*),name  from inst_process
      where state='3'
      and started>to_date('2007-02-24','yyyy-MM-dd')
      group by name

4:  迁钢公司拟搞人,拟搞结束流程数查询

     select count(*) num,initiator,b.name
 from inst_process a,
  orgmodel_userinfo b
 where
 state='4'
       and started>to_date('2007-02-24','yyyy-MM-dd')
 and a.initiator = b.id
 group by initiator,b.name
 order by num

5:  迁钢公司拟搞人,拟搞运行流程数查询

     select count(*) num,initiator,b.name
 from inst_process a,
  orgmodel_userinfo b
 where
 state='3'
       and started>to_date('2007-02-24','yyyy-MM-dd')
 and a.initiator = b.id
 group by initiator,b.name
 order by num

6:  部门结束流程汇总查询

      select
  sum(num),
  g.parentid,
  h.name
  from
  (
    select count(*) num,initiator,b.name,c.orgid
    from inst_process a,
     orgmodel_userinfo b,
     orgmodel_usertoorg c
    where
    state='4'
          and started>to_date('2007-02-24','yyyy-MM-dd')
    and a.initiator = b.id
    and b.id = c.userid and c.isprimary='1'
    group by initiator,b.name,c.orgid
    order by num
 ) e,orgmodel_orginfo f,orgmodel_orgtoorg g,orgmodel_orginfo h
 where e.orgid=f.id and f.id=g.childid
       and g.parentid=h.id
 group  by g.parentid,h.name

 7:  部门运行流程汇总查询

      select
  sum(num),
  g.parentid,
  h.name
  from
  (
    select count(*) num,initiator,b.name,c.orgid
    from inst_process a,
     orgmodel_userinfo b,
     orgmodel_usertoorg c
    where
    state='3'
          and started>to_date('2007-02-24','yyyy-MM-dd')
    and a.initiator = b.id
    and b.id = c.userid and c.isprimary='1'
    group by initiator,b.name,c.orgid
    order by num
 ) e,orgmodel_orginfo f,orgmodel_orgtoorg g,orgmodel_orginfo h
 where e.orgid=f.id and f.id=g.childid
       and g.parentid=h.id
 group  by g.parentid,h.name

 8: 结束流程处理查询
 
 select (completed-started) worktime, a.* from inst_process a

 where state='4'
           and started>to_date('2007-02-24','yyyy-MM-dd')
 order by worktime desc

 
 9: 流程平均处理时间

     select
     sum(worktime)/count(*)
     from
     (select
       (completed - started) worktime,
       a. *
     from inst_process a
     where state = '4'
           and started > to_date('2007-02-24', 'yyyy-MM-dd')
     order by worktime desc
     ) a

 
 9: 流程分类平均处理时间

 select
 sum(worktime)/count(*),
        name
        from
        (select
         (completed - started) worktime,
         a. *
         from inst_process a
         where state = '4'
           and started > to_date('2007-02-24', 'yyyy-MM-dd')
        order by worktime desc
        ) a
       group by name

 10:流程最快处理时间

     参靠8

 11:流程最慢处理时间
   
     参靠8

select
  sum(e.maxworktime)/count(*),
  g.parentid,
  h.name
  from
  (
    select min(completed-started) minworktime, max(completed-started) maxworktime,initiator,b.name,c.orgid
    from inst_process a,
     orgmodel_userinfo b,
     orgmodel_usertoorg c
    where
    state='4'
          and started>to_date('2007-02-24','yyyy-MM-dd')
    and a.initiator = b.id
    and b.id = c.userid and c.isprimary='1'
    group by initiator,b.name,c.orgid
  
 ) e,orgmodel_orginfo f,orgmodel_orgtoorg g,orgmodel_orginfo h
 where e.orgid=f.id and f.id=g.childid
       and g.parentid=h.id
 group  by g.parentid,h.name

select
  sum(e.minworktime)/count(*),
  max(e.minworktime),
  g.parentid,
  h.name
  from
  (
    select (completed-started) minworktime,initiator,b.name,c.orgid
    from inst_process a,
     orgmodel_userinfo b,
     orgmodel_usertoorg c
    where
    state='4'
          and started>to_date('2007-02-24','yyyy-MM-dd')
    and a.initiator = b.id
    and b.id = c.userid and c.isprimary='1'
    --group by initiator,b.name,c.orgid
  
 ) e,orgmodel_orginfo f,orgmodel_orgtoorg g,orgmodel_orginfo h
 where e.orgid=f.id and f.id=g.childid
       and g.parentid=h.id
 group  by g.parentid,h.name

13.所有流程的流程节点经过部门的平均时间
select
 sum(worktime)/count(*),
 count(*),
 g.name
 --funname
 from 
(
  select
  e.*,
  DECODE(trim(f.parentid),'00000001',f.childid,f.parentid) pp
  --f.parentid pp
  from
  (
    select a.name funname, b.userid, (b.taskendtime - b.taskreceivetime) worktime,c.orgid
    from inst_process a, oa_tasklist b, orgmodel_usertoorg c
    where state = 4
    and a.started>to_date('2007-02-24','yyyy-MM-dd')
    and a.instprocessid = b.instanceid
    and b.userid = c.userid and
         c.isprimary = '1'
   ) e,orgmodel_orgtoorg f
   
   where e.orgid=f.childid
 ) h,orgmodel_orginfo g
 where pp=g.id
 group by  g.name
14.处长的处理时间
select
  sum(taskendtime-taskstarttime)/count(*)
  from
  (
    select
  
     a.instprocessid,
     b.taskstarttime,
     b.taskendtime,
     b.userid
    from inst_process a, oa_tasklist b
    where a.instprocessid = b.instanceid
    and  state='4' and a.started>to_date('2007-02-24','yyyy-MM-dd')
  ) c,  (select
   distinct
   a.name rolename,
   b.userid
   from orgmodel_roleinfo  a,orgmodel_usertorole b
   where a.id = b.roleid
   and a.name='处长') d
   where c.userid=d.userid