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