SET SQL_MODE = '';
CREATE TEMPORARY TABLE temp_date_range AS SELECT em.EMP_NUMBER as emp_code, dr.date_range, em.SHIFT as shift, em.OVER_TIME as OT, em.UNIT  FROM emp_master em CROSS JOIN date_range dr WHERE  MONTH(dr.date_range)=6 AND YEAR(dr.date_range)=2025 AND em.ACTIVE_STATUS="Y" AND em.EMP_NUMBER= 'E-0273'  AND em.JOIN_DATE <=dr.date_range AND (em.RESIG_DATE is null OR em.RESIG_DATE >=dr.date_range) AND em.salary_REQ = "Y";

SELECT 
    a.emp_code,
    IFNULL(SUM(CASE 
                WHEN el.days > 0  AND el.leave_status = "A" AND el.approved_leave_type = "EL"  
               THEN IFNULL(el.days,0) - IFNULL(mp.day_pr,0)
             END), 0) AS EL,

    IFNULL(hlm_el.pay_status, "N") AS EL_PAY,
    
    IFNULL(SUM(CASE 
                WHEN el.days > 0 AND el.leave_status = "A" AND el.approved_leave_type = "CL" 
                     #AND a.date_range NOT IN (SELECT map.date FROM mach_punch map WHERE map.date = el.start_date AND map.emp_cd = el.emp_no)
                THEN  IF((IFNULL(db.leave_percent, 0) - mp.day_pr) < 0, 0, (IFNULL(db.leave_percent, 0) - mp.day_pr))
             END), 0) AS CL,
    IFNULL(hlm_cl.pay_status, "N") AS CL_PAY,
    
    IFNULL(SUM(CASE 
                WHEN el.days > 0 AND el.leave_status = "A" AND el.approved_leave_type = "SL" 
                     #AND a.date_range NOT IN (SELECT map.date FROM mach_punch map WHERE map.date = el.start_date AND map.emp_cd = el.emp_no)
                THEN  IF((IFNULL(db.leave_percent, 0) - mp.day_pr) < 0, 0, (IFNULL(db.leave_percent, 0) - mp.day_pr))
             END), 0) AS SL,
    IFNULL(hlm_sl.pay_status, "N") AS SL_PAY,
    
    IFNULL(SUM(CASE 
                WHEN el.days > 0 AND el.leave_status = "A" AND el.approved_leave_type = "LW" AND DAYOFWEEK(a.date_range) != 1 AND hd.holiday_type IS NULL
                THEN  IF((IFNULL(db.leave_percent, 0) - mp.day_pr) < 0, 0, (IFNULL(db.leave_percent, 0) - mp.day_pr))
             END), 0) AS LW,

    IFNULL(hlm_lw.pay_status, "N") AS LW_PAY,
    
    IFNULL(SUM(CASE WHEN hd.holiday_type="HD" AND DAYOFWEEK(a.date_range) != 1 THEN 1 END),0) as "HD",
"Y" as HD_PAY,

IFNULL(SUM(CASE WHEN hd.holiday_type="FO" AND DAYOFWEEK(a.date_range) != 1 THEN 1 END),0) as "FO",
"Y" as FO_PAY,
    
    IFNULL(SUM(CASE 
                WHEN od.type = "WFH" AND a.date_range BETWEEN od.from_dt AND od.to_dt AND od.app_by IS NOT NULL AND DAYOFWEEK(a.date_range) != 1 
                THEN 1 
             END), 0) AS WFH,
    "Y" AS WFH_PAY,

    IFNULL(SUM(CASE 
                WHEN od.type = "OD" AND a.date_range BETWEEN od.from_dt AND od.to_dt AND od.app_by IS NOT NULL AND DAYOFWEEK(a.date_range) != 1 
                THEN 1 
             END), 0) AS OD,
    "Y" AS OD_PAY,
    

    SUM(CASE WHEN DAYOFWEEK(a.date_range) = 1 THEN 1 END) AS "WO",

    SUM(CASE 
            WHEN DAYOFWEEK(a.date_range) = 1 
                 AND IFNULL((SELECT MAX(mp1.day_pr) FROM mach_punch mp1 WHERE mp1.emp_cd = a.emp_code AND mp1.date = DATE_SUB(a.date_range, INTERVAL 1 DAY)), 0) <= 0 
                 AND IFNULL((SELECT MAX(mp1.day_pr) FROM mach_punch mp1 WHERE mp1.emp_cd = a.emp_code AND mp1.date = DATE_ADD(a.date_range, INTERVAL 1 DAY)), 0) <= 0 
            THEN 1 
            ELSE 0 
        END) AS WO_UP,

    SUM(IFNULL((CASE WHEN (a.OT = "N" AND DAYOFWEEK(a.date_range) = 1) OR (a.OT = "N" AND DAYOFWEEK(a.date_range) != 1 AND hd.holiday_type IN ("HD", "FO")) THEN mp.day_pr ELSE 0 END), 0)) AS WOA,
    "Y" AS WOA_PAY,
    
    #SUM(IFNULL((CASE WHEN a.OT = "N" AND DAYOFWEEK(a.date_range) != 1 AND hd.holiday_type IN ("HD", "FO") THEN mp.day_pr ELSE 0 END), 0)) AS HO_FO_ADJ,
    #"Y" AS HO_FO_ADJ_PAY,

    SUM(IF(mp.day_pr > 0 AND DAYOFWEEK(a.date_range) != 1 AND a.date_range NOT IN (SELECT hd.holiday FROM hr_holiday_mast hd), mp.day_pr, 0)) AS "PR",

    DAYOFMONTH(LAST_DAY(a.date_range)) AS "DAYS",
    
    IFNULL(SUM(CASE WHEN IFNULL(gp.deduction,0)>0 THEN gp.deduction ELSE 0 END),0) as "GP_DED"

FROM 
    temp_date_range a 
LEFT JOIN 
    mach_punch mp ON mp.date = a.date_range AND mp.emp_cd = a.emp_code 
LEFT JOIN 
    od_application od ON od.emp_cd = a.emp_code AND a.date_range BETWEEN od.from_dt AND od.to_dt AND od.app_by IS NOT NULL AND od.type IN ("WFH", "OD")
LEFT JOIN
    hr_holiday_mast hd ON hd.holiday=a.date_range AND hd.holiday_type IN ("FO","HD")
LEFT JOIN 
    hr_emp_leaves el ON el.emp_no = a.emp_code AND el.start_date = a.date_range AND el.approved_leave_type IN ("LW", "EL","CL","SL") AND el.leave_status = "A"

LEFT JOIN gate_pass gp ON gp.emp_cd=a.emp_code AND gp.type="P" AND gp.date=a.date_range AND gp.status="A"

LEFT JOIN 
    hrm_day_breakup db ON db.code = el.leave_day
LEFT JOIN hrm_leave_mst hlm_lw ON hlm_lw.code = "LW" AND hlm_lw.unit_cd = a.UNIT
LEFT JOIN hrm_leave_mst hlm_el ON hlm_el.code = "EL" AND hlm_el.unit_cd = a.UNIT
LEFT JOIN hrm_leave_mst hlm_cl ON hlm_cl.code = "CL" AND hlm_cl.unit_cd = a.UNIT
LEFT JOIN hrm_leave_mst hlm_sl ON hlm_sl.code = "SL" AND hlm_sl.unit_cd = a.UNIT

WHERE 1=1
GROUP BY 
    a.emp_code 
ORDER BY 
    a.date_range, a.emp_code ASC

this is my query do not change anything . I am facing a problem that my complete day_pr is 1 but i am present half day which is 0.5 and rest of the half my el is applied which is 0.5 now this query is subtracting my half daypresent 0.5 and el (0.5) and returing me 0 but it should return 0.5