浙江03工作日志按人员查询 sql

最新推荐文章于 2023-02-22 18:06:16 发布

等烟雨

最新推荐文章于 2023-02-22 18:06:16 发布

阅读量118

点赞数

CC 4.0 BY-SA版权

分类专栏：工作日志

本文链接：https://blog.youkuaiyun.com/meteor_1988/article/details/84286113

工作日志专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一种使用SQL进行员工工作日志统计的方法，包括不同类型的工作日志数量统计及工作表现评估。通过对日志类型的计数和评价等级的划分，实现了对员工每日、每周、每月及每季度的工作量及质量进行量化分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

with a as (
select acm.name username,
       count(worklog.logtypeid) totallog,
       sum(decode(worklog.logtypeid,'ec229560-f915-449d-ae35-3f60903c9197',1,0)) dailylog,
       sum(decode(worklog.logtypeid,'75782b36-e415-4fe9-ad3b-82a17235e475',1,0)) weeklog,
       sum(decode(worklog.logtypeid,'5bfa76f4-c677-4b4f-825b-783ec88bce79',1,0)) monthlog,
       sum(decode(worklog.logtypeid,'e6f2edf8-a737-41c2-b478-21f60498584d',1,0)) quarterlog,
       sum(decode(worklog.businesstypeid,'63f61fc0-cc19-4036-b19c-57be635887da',1,0))overtime,
       sum(case when log_eva.evaluate >85 then 1 else 0 end) exccelent,
       sum(case when log_eva.evaluate between 76 and 85 then 1 else 0 end) good,
       sum(case when log_eva.evaluate between 50 and 75 then 1 else 0 end) pass,
       sum(case when log_eva.evaluate >50 then 1 else 0 end) nopass  
  from acm_user acm
 inner join (select t.id ,t.name,t.parent_id from acm_department t
                   start with t.id = '37c70b89-4430-470c-93b6-6bf6004ded29'
                   connect by prior t.id = t.parent_id) dept 
    on acm.dept_id = dept.id
 left join itil_worklog worklog
        on worklog.creator = acm.id
 left join ( select logid,avg(decode(evaluate,'优秀',100,'良好',80,'合格',60,'不合格',40)) evaluate
               from itil_logaudit_relation
              group by logid ) log_eva
       on worklog.id = log_eva.logid          

       where worklog.createtime between to_date('2012-07-07 00:00','yyyy-mm-dd hh24:mi:ss') 
                                and to_date('2012-09-07 23:59','yyyy-mm-dd hh24:mi:ss')
                                 or worklog.createtime is null 
                             
   group by acm.name 
   order by totallog desc
)  
   
select acm.name,
       nvl(a.totallog,0) totallog,
       nvl(a.dailylog,0) dailylog,
       nvl(a.weeklog,0) weeklog ,
       nvl(a.monthlog,0) monthlog, 
       nvl(a.quarterlog,0) quarterlog, 
       nvl(a.overtime,0) overtime, 
       nvl(a.exccelent,0) exccelent, 
       nvl(a.good,0) good, 
       nvl(a.pass,0) pass,
       nvl(a.nopass,0) nopass
  from a  
  right join acm_user acm on a.username = acm.name 
  inner join (select t.id ,t.name,t.parent_id from acm_department t
                   start with t.id = '37c70b89-4430-470c-93b6-6bf6004ded29'
                   connect by prior t.id = t.parent_id) dept
    on acm.dept_id = dept.id