Hi... I have three types of night premium and it only computed of the schedule is night shift I have this Night Premium Types: - NightPremiumRegular (Monday-Saturday) -NightPremiumSunday (Sunday) - NightPremiumHoliday I created table which has a listed of holidays date. Now I encountered problem in sum of hours in NightPremiumHoliday here is my code: Code: //===================Display Night Premium========================= $sql = "SELECT r.EMP_NO, r.LOGIN, DATE(LOGIN) AS DATE_LOGIN, DATE_FORMAT(LOGIN, '%W') AS SUNDAY, r.LOGOUT, sec_to_time(SUM(time_to_sec(Rendered))) AS Rendered FROM $PAYROLL.reg_att r, $ADODB_DB.employment em, $ADODB_DB.personal p WHERE time_to_sec(time(LOGIN)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('09:35:00') AND r.EMP_NO = em.EMP_NO AND em.EMP_ID = '$currentEmpID' AND p.EMP_ID = '$currentEmpID' AND DATE_FORMAT(LOGIN, '%W') = 'Sunday'"; $rsNPSun = $conn2->Execute($sql); $NPSunHours = $rsNPSun->fields['Rendered']; $NPSunDate = $rsNPSun->fields['SUNDAY']; $NPSunHours = substr($NPSunHours, 0, 5); $NPSunHours = str_replace(':', '.', $NPSunHours); $sql = "SELECT OP FROM ot WHERE Category = 'NightPremiumSunday'"; $rsOTCatSun = $conn2->Execute($sql); $NPSun_OP = $rsOTCatSun->fields['OP']; $NPSunAmt = (($Rate / 8 * 1.35 * $NPSun_OP) * $NPSunHours); $NPSunAmt = number_format($NPSunAmt, 2, '.', ''); $smarty->assign('NPSunHours', $NPSunHours); $smarty->assign('NPSunAmt', $NPSunAmt); //=================NP REG======= $sql = "SELECT r.EMP_NO, r.LOGIN, DATE(LOGIN) AS DATE_LOGIN, DATE_FORMAT(LOGIN, '%W') AS Week_Days, r.LOGOUT, sec_to_time(SUM(time_to_sec(Rendered))) AS Rendered FROM $PAYROLL.reg_att r, $ADODB_DB.employment em, $ADODB_DB.personal p WHERE time_to_sec(time(LOGIN)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('09:35:00') AND r.EMP_NO = em.EMP_NO AND em.EMP_ID = '$currentEmpID' AND p.EMP_ID = '$currentEmpID' AND DATE_FORMAT(LOGIN, '%W') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')"; $rsNPReg = $conn2->Execute($sql); $NPRegHours = $rsNPReg->fields['Rendered']; $NPRegDate = $rsNPReg->fields['Week_Days']; $NPRegHours = substr($NPRegHours, 0, 5); $NPRegHours = str_replace(':', '.', $NPRegHours); $sql = "SELECT OP FROM ot WHERE Category = 'NightPremiumRegular'"; $rsOTCatSun = $conn2->Execute($sql); $NPReg_OP = $rsOTCatSun->fields['OP']; $NPRegAmt = (($Rate / 8 * $NPReg_OP) * $NPRegHours); $NPRegAmt = number_format($NPRegAmt, 2, '.', ''); $smarty->assign('NPRegHours', $NPRegHours); $smarty->assign('NPRegAmt', $NPRegAmt); //=======================Night Premium Holiday============== $sql = "SELECT r.EMP_NO, r.LOGIN, DATE(LOGIN) AS DATE_LOGIN, r.LOGOUT, sec_to_time(SUM(time_to_sec(Rendered))) AS Rendered FROM $PAYROLL.reg_att r, $ADODB_DB.employment em, $PAYROLL.holiday_date h WHERE time_to_sec(time(LOGIN)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('09:35:00') AND DATE(LOGIN) = h.holiday_date AND r.EMP_NO = em.EMP_NO AND em.EMP_ID = '$currentEmpID'"; $rsNPHol = $conn2->Execute($sql); $NPHolHours = $rsNPHol->fields['Rendered']; $NPHolHours = substr($NPHolHours, 0, 5); $NPHolHours = str_replace(':', '.', $NPHolHours); $sql = "SELECT OP FROM ot WHERE Category = 'NightPremiumHoliday'"; $rsOTCatSun = $conn2->Execute($sql); $NPHol_OP = $rsOTCatSun->fields['OP']; $NPHolAmt = (($Rate / 8 * 2.05 * $NPHol_OP) * $NPHolHours); $NPHolAmt = number_format($NPHolAmt, 2, '.', ''); $smarty->assign('NPHolHours', $NPHolHours); $smarty->assign('NPHolAmt', $NPHolAmt); $NPHours = ($NPRegHours + $NPSunHours + $NPHolHours); $NPHours = number_format($NPHours, 2, '.', ''); $NP_Amt = ($NPRegAmt + $NPSunAmt + $NPHolAmt); $NP_Amt = number_format($NP_Amt, 2, '.', ''); $smarty->assign('NP_Hours', $NPHours); $smarty->assign('NP_Amt', $NP_Amt); for example: I have attendance 2012-01-01 and it's sunday and it is also holiday.. And the rendered is 8 so the NightPremiumSunday and NightPremiumHolidays computed, but i got problem in computations of NPHours. I got NPHours = 16, supposively 8, but because it is sunday and holiday it sum the result of hours in Sunday and Holiday.. How can I only get 8 hours if my date is holiday? Thank you so much.. Any help is highly appreciated.. feel free to ask me if theirs a question.