SELECT ADATE, LBNO, NVL(SUM(HALF1), 0)+NVL(SUM(HALF2), 0) RG FROM MRELHR.HR_ATTND_DTLS WHERE COMP_CODE = :COMP_CODE AND ADATE BETWEEN :FDATE AND :TDATE HAVING NVL(SUM(HALF1), 0)+NVL(SUM(HALF2), 0) > 8 GROUP BY ADATE, LBNO SELECT ADATE, LBNO, SHIFT_CODE, NVL(SUM(HALF1), 0)+NVL(SUM(HALF2), 0)+NVL(SUM(OT_HRS*2), 0) TH FROM MRELHR.HR_ATTND_DTLS WHERE COMP_CODE = :COMP_CODE AND ADATE BETWEEN :FDATE AND :TDATE HAVING NVL(SUM(HALF1), 0)+NVL(SUM(HALF2), 0)+NVL(SUM(OT_HRS*2), 0) > 8.5 GROUP BY ADATE, LBNO, SHIFT_CODE SELECT LBNO, NVL(SUM(HALF1), 0) + NVL(SUM(HALF2), 0) + NVL(SUM(N_A), 0) RH FROM ( SELECT LBNO, NVL(HALF1, 0) HALF1, NVL(HALF2, 0) HALF2, CASE WHEN SHIFT_CODE = 'C' AND NVL(HALF1, 0) = 8 THEN 0 ELSE NVL(N_A, 0) END N_A FROM MRELHR.HR_ATTND_DTLS WHERE COMP_CODE = :COMP_CODE AND ADATE BETWEEN :FDATE AND :TDATE AND DAY_OFF IS NULL ) HAVING NVL(SUM(HALF1), 0) + NVL(SUM(HALF2), 0) + NVL(SUM(N_A), 0) > 112 GROUP BY LBNO