时间戳

首页 > 解决方案 > 获取 ORACLE SQL 开发人员中单个员工的总出勤率

oracle-sqldeveloper - 获取 ORACLE SQL 开发人员中单个员工的总出勤率

问题描述

INSERT INTO COD_HRM_ADVICED_MONTH_DETAIL
        (EMPLOYEE_ID,MONTH_ID,TOTAL_PRESENT,TOTAL_ABSENT,TOTAL_LEAVE,ACTION_ON,ACTION_BY,ACTION_TYPE,PROJECT_ID,COMPANY_ID)
        SELECT A.EMPLOYEE_ID,'25',
        (SELECT  COUNT(*) FROM COD_HRM_ATTENDANCE 
        WHERE ATTENDANCE_TYPE='P' AND to_char(ATTENDANCE_DATE, 'mm')='08' 
        AND to_char(ATTENDANCE_DATE, 'yy')='21' 
        AND  EMPLOYEE_ID=A.EMPLOYEE_ID) AS PRESENT,
        (SELECT  COUNT(*) FROM COD_HRM_ATTENDANCE 
        WHERE ATTENDANCE_TYPE='A' AND to_char(ATTENDANCE_DATE, 'mm')='08' 
        AND to_char(ATTENDANCE_DATE, 'yy')='21' 
        AND EMPLOYEE_ID=A.EMPLOYEE_ID) AS ABSENT,
        (SELECT  COUNT(*) FROM COD_HRM_ATTENDANCE 
        WHERE ATTENDANCE_TYPE='L' AND to_char(ATTENDANCE_DATE, 'mm')='' AND EMPLOYEE_ID=A.EMPLOYEE_ID) AS LEAVE,
        '10-Aug-21','1','Insert','4','2' 
        FROM COD_HRM_ATTENDANCE A GROUP BY EMPLOYEE_ID

我得到了所有目前缺勤和离开员工的详细信息，现在我想对每个员工的这些列求和。

1：sql表快照

标签： oracle-sqldeveloper

解决方案

    SELECT A.EMPLOYEE_ID,
    (SELECT  COUNT(*) FROM COD_HRM_ATTENDANCE 
    WHERE ATTENDANCE_TYPE='P' AND to_char(ATTENDANCE_DATE, 'mm')='08' 
    AND to_char(ATTENDANCE_DATE, 'yy')='21' 
    AND  EMPLOYEE_ID=A.EMPLOYEE_ID) AS PRESENT,
    (SELECT  COUNT(*) FROM COD_HRM_ATTENDANCE 
    WHERE ATTENDANCE_TYPE='A' AND to_char(ATTENDANCE_DATE, 'mm')='08' 
    AND to_char(ATTENDANCE_DATE, 'yy')='21' 
    AND EMPLOYEE_ID=A.EMPLOYEE_ID) AS ABSENT,
    (SELECT  COUNT(*) FROM COD_HRM_ATTENDANCE 
    WHERE ATTENDANCE_TYPE='L' AND to_char(ATTENDANCE_DATE, 'mm')='08' AND EMPLOYEE_ID=A.EMPLOYEE_ID) AS LEAVE
    ,(SELECT  COUNT(*) FROM COD_HRM_ATTENDANCE 
    WHERE  to_char(ATTENDANCE_DATE, 'mm')='08' AND EMPLOYEE_ID=A.EMPLOYEE_ID) AS TOTAL
    FROM COD_HRM_ATTENDANCE A GROUP BY EMPLOYEE_ID

我通过全部计数解决了这个问题，没有给出任何出勤类型

推荐阅读