CODEWITHSUNDEEP
oracle-sqldeveloper
faisal satti
faisal satti

Reputation: 23

GET SUM OF TOTAL ATTENDANCE OF SINGLE EMPLOYEE IN ORACLE SQL DEVELOPER

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

I get the detail of all the present absent and leave employee in different column now I want to sum of these column for every single employee.

1: sql table snap

Upvotes: 0

Views: 56

Answers (1)

faisal satti
faisal satti

Reputation: 23

    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

i solved this problem by count all without giving any attendance type

Upvotes: 1

Related Questions