人效报表，获取同一人每天的处理数据的情况，同时分时段显示

女爷i 2022-05-15 14:17 146阅读 0赞

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d6bjEwNTQxNjIyMjk_size_16_color_FFFFFF_t_70

当天任务数：按订单的审核记录表的中任务开始时间统计。当天相同订单多次提交，只计第一次提交；

当天及时完成数=当天任务数中的订单，任务开始时间+24小时>=该订单审核的结束时间 的订单数量；

上班段通过页数：当天订单通过时间在上班时间段内（9:00-18:00）的所有订单的页数总和（一个订单多次通过的，仅计一次通过），下班时间段同理；

上班段驳回页数：当天订单驳回时间在上班时间段内（9:00-18:00）的所有订单的页数总和（驳回时间>第一次通过时间的，不计），下班时间段同理。

这次报表的思路在于：在同一张表“审核表”中取数据时候，先取时间段的数据出来，然后将这六条要求写成相对应的查询语句，再用LEFT JOIN（使用右外连接应该也可以，本人没有试过）连接起来取每一天的数据，最后把整个连接表当做一个新表进行查询。这样子查询出来的数据就是一张列表，在mybatis中使用map类型返回值，在控制器中用list封装，即可读取出数据。

（还有第二种思路：先取出每一天的数据，再取一个时间段的数据，即先部分后整体。但是这样子要使用oracle循环，写存储过程，比较繁琐，有兴趣的朋友可以尝试一下）

以下是具体的SQL语句：

  1.     SELECT CU.NAME,  
  2.          A\_TIME,   
  3.          NVL(COUNT(TASK\_COUNT),0) TASK\_COUNT,   
  4.           NVL(COUNT(COMPLETE\_TASK\_COUNT),0) COMPLETE\_TASK\_COUNT,   
  5.          NVL(SUM(PASS\_COUNT\_WORK),0) PASS\_COUNT\_WORK,   
  6.          NVL(SUM(PASS\_COUNT\_NO\_WORK),0) PASS\_COUNT\_NO\_WORK,   
  7.          NVL(SUM(REJECT\_COUNT\_WORK),0) REJECT\_COUNT\_WORK,   
  8.          NVL(SUM(REJECT\_COUNT\_NO\_WORK),0) REJECT\_COUNT\_NO\_WORK,
  10.          NVL(SUM(PASS\_COUNT\_WORK),0)+NVL(SUM(PASS\_COUNT\_NO\_WORK),0) PASS\_WORK,  
  11.          NVL(SUM(REJECT\_COUNT\_WORK),0)+NVL(SUM(REJECT\_COUNT\_NO\_WORK),0) REJECT\_WORK,  
  12.          NVL(SUM(PASS\_COUNT\_WORK),0)+NVL(SUM(PASS\_COUNT\_NO\_WORK),0)+NVL(SUM(REJECT\_COUNT\_WORK),0)+NVL(SUM(REJECT\_COUNT\_NO\_WORK),0) ALL\_COUNT,
  14.          ROUND(NVL(COUNT(COMPLETE\_TASK\_COUNT),0)/NVL(COUNT(TASK\_COUNT),0),3) COMPLETE\_EFFICIENCY,  
  15.          ROUND((NVL(SUM(PASS\_COUNT\_WORK),0)+NVL(SUM(PASS\_COUNT\_NO\_WORK),0))/(NVL(SUM(PASS\_COUNT\_WORK),0)+NVL(SUM(PASS\_COUNT\_NO\_WORK),0)+NVL(SUM(REJECT\_COUNT\_WORK),0)+NVL(SUM(REJECT\_COUNT\_NO\_WORK),0)),3) PASS\_EFFICIENCY  
  16.     FROM  
  17.     (  
  18.     SELECT A.A\_ORDER\_ID, A.RECEIVING\_LERK\_ID, A.A\_TIME, A.TASK\_COUNT,   
  19.                  B.COMPLETE\_TASK\_COUNT,  
  20.                  C.PASS\_COUNT\_WORK,  
  21.                  D.PASS\_COUNT\_NO\_WORK,  
  22.                  E.REJECT\_COUNT\_WORK,  
  23.                  F.REJECT\_COUNT\_NO\_WORK  
  24.     FROM  
  25.     (//当天任务数  
  26.         select count(0) TASK\_COUNT,to\_char(TASK\_START\_TIME,'yyyy-MM-dd') A\_TIME,t1.RECEIVING\_LERK\_ID ,T1.ORDER\_ID A\_ORDER\_ID  
  27.         from CRM\_ORDER\_AUDIT t1   
  28.         where t1.TASK\_START\_TIME >= to\_date(\#\{startDate\},'yyyy-MM-dd')   
  29.         and t1.TASK\_START\_TIME <=  to\_date(\#\{endDate\},'yyyy-MM-dd')   
  30.         AND t1.AUDIT\_COUNT = 1  
  31.         GROUP BY to\_char(T1.TASK\_START\_TIME,'yyyy-MM-dd'),t1.RECEIVING\_LERK\_ID,T1.ORDER\_ID  
  32.     ) A  
  33.     LEFT JOIN  
  34.     (//当天及时完成数  
  35.         SELECT COUNT(C.ID) COMPLETE\_TASK\_COUNT,C.RECEIVING\_LERK\_ID,C.ORDER\_ID B\_ORDER\_ID  
  36.         from CRM\_ORDER\_AUDIT C  
  37.         where   
  38.         C.TASK\_START\_TIME >= to\_date(\#\{startDate\},'yyyy-MM-dd')   
  39.         and C.TASK\_START\_TIME <=  to\_date(\#\{endDate\},'yyyy-MM-dd')   
  40.         AND (C.TASK\_START\_TIME < C.END\_TIME)  
  41.         AND ((to\_char(C.END\_TIME,'yyyy-mm-dd HH24:MI:SS')) < to\_char(C.TASK\_START\_TIME+1,'yyyy-mm-dd HH24:MI:SS'))  
  42.         AND C.AUDIT\_COUNT = 1  
  43.         GROUP BY C.RECEIVING\_LERK\_ID,C.ORDER\_ID  
  44.     ) B ON (A.RECEIVING\_LERK\_ID = B.RECEIVING\_LERK\_ID AND A.A\_ORDER\_ID = B.B\_ORDER\_ID)  
  45.     LEFT JOIN  
  46.     (//上班段通过页数  
  47.         SELECT SUM(A.TOTAL\_COUNT) PASS\_COUNT\_WORK, RECEIVING\_LERK\_ID, ORDER\_ID C\_ORDER\_ID  
  48.         FROM CRM\_ORDER\_AUDIT A   
  49.         WHERE A.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
  50.         AND A.TYPE = 1  
  51.         AND (to\_char(A.END\_TIME,'HH24') >= 09)  
  52.         AND (to\_char(A.END\_TIME,'HH24') <= 18)  
  53.         AND A.AUDIT\_COUNT IN (  
  54.             SELECT MIN(B.AUDIT\_COUNT) FROM CRM\_ORDER\_AUDIT B   
  55.             WHERE B.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
  56.             AND B.TYPE = 1  
  57.             AND A.ORDER\_ID = B.ORDER\_ID  
  58.             AND (to\_char(B.END\_TIME,'HH24') >= 09)  
  59.             AND (to\_char(B.END\_TIME,'HH24') <= 18)  
  60.         )  
  61.         GROUP BY RECEIVING\_LERK\_ID,ORDER\_ID  
  62.     ) C ON (A.RECEIVING\_LERK\_ID = C.RECEIVING\_LERK\_ID AND A.A\_ORDER\_ID = C.C\_ORDER\_ID)  
  63.     LEFT JOIN  
  64.     (//下班段通过页数  
  65.         SELECT SUM(A.TOTAL\_COUNT) PASS\_COUNT\_NO\_WORK, RECEIVING\_LERK\_ID, ORDER\_ID D\_ORDER\_ID  
  66.         FROM CRM\_ORDER\_AUDIT A   
  67.         WHERE A.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
  68.         AND A.TYPE = 1  
  69.         AND (to\_char(A.END\_TIME,'HH24') >= 00 AND to\_char(A.END\_TIME,'HH24') < 09)  
  70.         OR (to\_char(A.END\_TIME,'HH24') > 18 AND to\_char(A.END\_TIME,'HH24') < 24)  
  71.         AND A.AUDIT\_COUNT IN (  
  72.             SELECT MIN(B.AUDIT\_COUNT) FROM CRM\_ORDER\_AUDIT B   
  73.             WHERE B.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
  74.             AND B.TYPE = 1  
  75.             AND A.ORDER\_ID = B.ORDER\_ID  
  76.             AND (to\_char(B.END\_TIME,'HH24') >= 00 AND to\_char(B.END\_TIME,'HH24') < 09)  
  77.             OR (to\_char(B.END\_TIME,'HH24') > 18 AND to\_char(B.END\_TIME,'HH24') < 24)  
  78.         )  
  79.         GROUP BY RECEIVING\_LERK\_ID,ORDER\_ID  
  80.     ) D ON (A.RECEIVING\_LERK\_ID = D.RECEIVING\_LERK\_ID AND A.A\_ORDER\_ID = D.D\_ORDER\_ID)  
  81.     LEFT JOIN  
  82.     (//上班段驳回页数  
  83.         SELECT SUM(A.REJECT\_COUNT) REJECT\_COUNT\_WORK, RECEIVING\_LERK\_ID, ORDER\_ID E\_ORDER\_ID  
  84.         FROM CRM\_ORDER\_AUDIT A   
  85.         WHERE A.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
  86.         AND A.TYPE = 0  
  87.         AND (to\_char(A.END\_TIME,'HH24') >= 09)  
  88.         AND (to\_char(A.END\_TIME,'HH24') <= 18)  
  89.         AND A.AUDIT\_COUNT IN (  
  90.             SELECT B.AUDIT\_COUNT FROM CRM\_ORDER\_AUDIT B   
  91.             WHERE B.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
  92.             AND B.TYPE = 0  
  93.             AND A.ORDER\_ID = B.ORDER\_ID  
  94.             AND (to\_char(B.END\_TIME,'HH24') >= 09)  
  95.             AND (to\_char(B.END\_TIME,'HH24') <= 18)  
  96.             AND B.END\_TIME <   
  97.             (  
  98.                 SELECT C.END\_TIME  
  99.                 FROM CRM\_ORDER\_AUDIT C  
  100.                 WHERE C.TYPE = 1 AND C.ID = (  
  101.                     SELECT MIN(D.ID) FROM CRM\_ORDER\_AUDIT D WHERE D.TYPE = 1 AND D.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd') AND B.ORDER\_ID = D.ORDER\_ID  
  102.                 )  
  103.             )  
  104.         )  
  105.         GROUP BY RECEIVING\_LERK\_ID,ORDER\_ID  
  106.     ) E ON (A.RECEIVING\_LERK\_ID = E.RECEIVING\_LERK\_ID AND A.A\_ORDER\_ID = E.E\_ORDER\_ID)  
  107.     LEFT JOIN  
  108.     (//下班段驳回页数  
  109.         SELECT SUM(A.REJECT\_COUNT) REJECT\_COUNT\_NO\_WORK, RECEIVING\_LERK\_ID, ORDER\_ID F\_ORDER\_ID  
  110.         FROM CRM\_ORDER\_AUDIT A   
  111.         WHERE A.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
  112.         AND A.TYPE = 0  
  113.         AND (to\_char(A.END\_TIME,'HH24') >= 00 AND to\_char(A.END\_TIME,'HH24') < 09)  
  114.         OR (to\_char(A.END\_TIME,'HH24') > 18 AND to\_char(A.END\_TIME,'HH24') < 24)  
  115.         AND A.AUDIT\_COUNT IN (  
  116.             SELECT B.AUDIT\_COUNT FROM CRM\_ORDER\_AUDIT B   
  117.             WHERE B.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd')  
  118.             AND B.TYPE = 0  
  119.             AND A.ORDER\_ID = B.ORDER\_ID  
  120.             AND (to\_char(A.END\_TIME,'HH24') >= 00 AND to\_char(A.END\_TIME,'HH24') < 09)  
  121.             OR (to\_char(A.END\_TIME,'HH24') > 18 AND to\_char(A.END\_TIME,'HH24') < 24)  
  122.             AND B.END\_TIME <   
  123.             (  
  124.                 SELECT C.END\_TIME  
  125.                 FROM CRM\_ORDER\_AUDIT C  
  126.                 WHERE C.TYPE = 1 AND C.ID = (  
  127.                     SELECT MIN(D.ID) FROM CRM\_ORDER\_AUDIT D WHERE D.TYPE = 1 AND D.END\_TIME BETWEEN to\_date(\#\{startDate\},'yyyy-MM-dd') AND to\_date(\#\{endDate\},'yyyy-MM-dd') AND B.ORDER\_ID = D.ORDER\_ID  
  128.                 )  
  129.             )  
  130.         )  
  131.         GROUP BY RECEIVING\_LERK\_ID,ORDER\_ID  
  132.     ) F ON (A.RECEIVING\_LERK\_ID = F.RECEIVING\_LERK\_ID AND A.A\_ORDER\_ID = F.F\_ORDER\_ID)  
  134.     GROUP BY A.A\_ORDER\_ID, A.TASK\_COUNT, A.A\_TIME, A.RECEIVING\_LERK\_ID,  
  135.                      B.COMPLETE\_TASK\_COUNT,  
  136.                      C.PASS\_COUNT\_WORK,  
  137.                      D.PASS\_COUNT\_NO\_WORK,  
  138.                      E.REJECT\_COUNT\_WORK,  
  139.                      F.REJECT\_COUNT\_NO\_WORK  
  140.     ORDER BY A.A\_TIME DESC  
  141.     ) combination   
  142.     LEFT JOIN CRM\_USER CU ON CU.ID = RECEIVING\_LERK\_ID  
  144.     GROUP BY A\_TIME,CU.NAME  
  145.     ORDER BY A\_TIME DESC

注：一下子看不懂的亲们，可以先将六条select查询拿出来看，是可以单独运行的。本人仅提供思路，因此不会提供“审核表”的具体参数说明。

发表评论

表情：
评论列表 （有 0 条评论，146人围观）

还没有评论，来说两句吧...

相关阅读