工作量统计

工作量统计SQL

SELECT A.*,A.rowid FROM TB_WORK_LOG A WHERE OPERMAN='周骏' and SYSTEM='WMS 1.0'  

   AND CREDATE >= TO_DATE('2024-2-1', 'yyyy-mm-dd hh24:mi:ss')
   AND CREDATE <= TO_DATE('2024-3-1', 'yyyy-mm-dd hh24:mi:ss')
   ORDER BY CREDATE



--本周
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND TRUNC(CREDATE, 'IW') = TRUNC(SYSDATE, 'IW');

--本月
  SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND TO_CHAR(CREDATE, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM');

--前6月
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -6), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -5), 'MM')
 UNION ALL
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -5), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -4), 'MM')
 UNION ALL
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -4), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM')  
      UNION ALL
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -2), 'MM')  
      UNION ALL
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -2), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')  
      UNION ALL
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, 0), 'MM')  



     --前6月早班工单
     with v_1 as (
SELECT  COUNT(*) COU ,OPERMAN,TO_CHAR(CREDATE, 'MM') AS CREDATE_MONTH
    FROM TB_WORK_LOG
   WHERE  1=1 
     --AND OPERMAN = '周骏'
     AND SYSTEM='WMS 1.0'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -6), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -5), 'MM')
     AND TO_CHAR(CREDATE, 'HH24:MI') >= '09:00'
     AND TO_CHAR(CREDATE, 'HH24:MI') <= '17:30'
     GROUP BY OPERMAN,CREDATE
     ),
   v_2 as (
SELECT  COUNT(*) COU ,OPERMAN,TO_CHAR(CREDATE, 'MM') AS CREDATE_MONTH
    FROM TB_WORK_LOG
   WHERE  1=1 
     --AND OPERMAN = '周骏'
     AND SYSTEM='WMS 1.0'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -5), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -4), 'MM')
     AND TO_CHAR(CREDATE, 'HH24:MI') >= '09:00'
     AND TO_CHAR(CREDATE, 'HH24:MI') <= '17:30'
     GROUP BY OPERMAN,CREDATE
     )
      select sum(v_1.COU) sumDiv,OPERMAN,CREDATE_MONTH  from v_1 group by OPERMAN,CREDATE_MONTH
          UNION ALL
      select sum(v_2.COU) sumDiv,OPERMAN,CREDATE_MONTH  from v_2 group by OPERMAN,CREDATE_MONTH




--前6月早班工单
SELECT DISTINCT COUNT(*),OPERMAN,TO_CHAR(CREDATE, 'MM') AS CREDATE_MONTH
    FROM TB_WORK_LOG
   WHERE  1=1 
     --AND OPERMAN = '周骏'
     AND SYSTEM='WMS 1.0'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -6), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -5), 'MM')
     AND TO_CHAR(CREDATE, 'HH24:MI') >= '09:00'
     AND TO_CHAR(CREDATE, 'HH24:MI') <= '17:30'
     GROUP BY OPERMAN,CREDATE
 UNION ALL
SELECT DISTINCT COUNT(*),OPERMAN,TO_CHAR(CREDATE, 'MM') AS CREDATE_MONTH
    FROM TB_WORK_LOG
   WHERE  1=1 
     --AND OPERMAN = '周骏'
     AND SYSTEM='WMS 1.0'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -5), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -4), 'MM')
     AND TO_CHAR(CREDATE, 'HH24:MI') >= '09:00'
     AND TO_CHAR(CREDATE, 'HH24:MI') <= '17:30'
     GROUP BY OPERMAN,CREDATE
 UNION ALL
SELECT DISTINCT COUNT(*),OPERMAN,TO_CHAR(CREDATE, 'MM') AS CREDATE_MONTH
    FROM TB_WORK_LOG
   WHERE  1=1 
     --AND OPERMAN = '周骏'
          AND SYSTEM='WMS 1.0'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -4), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM')
     AND TO_CHAR(CREDATE, 'HH24:MI') >= '09:00'
     AND TO_CHAR(CREDATE, 'HH24:MI') <= '17:30'
     GROUP BY OPERMAN,CREDATE
      UNION ALL
SELECT DISTINCT COUNT(*),OPERMAN,TO_CHAR(CREDATE, 'MM') AS CREDATE_MONTH
    FROM TB_WORK_LOG
   WHERE  1=1 
    -- AND OPERMAN = '周骏'
         AND SYSTEM='WMS 1.0'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -2), 'MM')
     AND TO_CHAR(CREDATE, 'HH24:MI') >= '09:00'
     AND TO_CHAR(CREDATE, 'HH24:MI') <= '17:30'
     GROUP BY OPERMAN,CREDATE
      UNION ALL
SELECT DISTINCT COUNT(*),OPERMAN,TO_CHAR(CREDATE, 'MM') AS CREDATE_MONTH
    FROM TB_WORK_LOG
   WHERE 1=1 
    -- AND OPERMAN = '周骏'
         AND SYSTEM='WMS 1.0'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -2), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
     AND TO_CHAR(CREDATE, 'HH24:MI') >= '09:00'
     AND TO_CHAR(CREDATE, 'HH24:MI') <= '17:30'
     GROUP BY OPERMAN,CREDATE
      UNION ALL
SELECT DISTINCT COUNT(*),OPERMAN,TO_CHAR(CREDATE, 'MM') AS CREDATE_MONTH
    FROM TB_WORK_LOG
   WHERE 1=1 
    -- AND OPERMAN = '周骏'
     AND SYSTEM='WMS 1.0'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, 0), 'MM')
     AND TO_CHAR(CREDATE, 'HH24:MI') >= '09:00'
     AND TO_CHAR(CREDATE, 'HH24:MI') <= '17:30'
     GROUP BY OPERMAN,CREDATE





--前6月晚班工单
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -6), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -5), 'MM')
     AND (TO_CHAR(CREDATE, 'HH24:MI') >= '17:30' OR TO_CHAR(CREDATE + INTERVAL '1' DAY, 'HH24:MI') <= '01:30')
 UNION ALL
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -5), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -4), 'MM')
     AND (TO_CHAR(CREDATE, 'HH24:MI') >= '17:30' OR TO_CHAR(CREDATE + INTERVAL '1' DAY, 'HH24:MI') <= '01:30')
 UNION ALL
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -4), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM')
     AND (TO_CHAR(CREDATE, 'HH24:MI') >= '17:30' OR TO_CHAR(CREDATE + INTERVAL '1' DAY, 'HH24:MI') <= '01:30')
      UNION ALL
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -2), 'MM')
     AND (TO_CHAR(CREDATE, 'HH24:MI') >= '17:30' OR TO_CHAR(CREDATE + INTERVAL '1' DAY, 'HH24:MI') <= '01:30')
      UNION ALL
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -2), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
     AND (TO_CHAR(CREDATE, 'HH24:MI') >= '17:30' OR TO_CHAR(CREDATE + INTERVAL '1' DAY, 'HH24:MI') <= '01:30')
      UNION ALL
SELECT COUNT(*)
    FROM TB_WORK_LOG
   WHERE OPERMAN = '周骏'
     AND CREDATE >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
     AND CREDATE < TRUNC(ADD_MONTHS(SYSDATE, 0), 'MM')
     AND (TO_CHAR(CREDATE, 'HH24:MI') >= '17:30' OR TO_CHAR(CREDATE + INTERVAL '1' DAY, 'HH24:MI') <= '01:30')