Late delete
select h.* |
delete h
from hrm_att_summary h, hrm_roster_allocation ro
where
h.emp_id=ro.PBI_ID
and h.leave_id=0
and h.att_date=ro.roster_date
and h.in_time > concat(h.att_date,' ',h.sch_in_time)
and h.att_date BETWEEN '2018-08-26' AND '2018-09-25' |
Early out delete |
#######################
Delete h
FROM hrm_att_summary h,personnel_basic_info p
WHERE
h.emp_id=p.PBI_ID
and h.out_time<concat(DATE_ADD(h.att_date, INTERVAL 1 DAY),' ',h.sch_out_time)
and h.sch_out_time<='06:00:00'
and h.leave_id=0
and h.iom_sl_no=0
and p.JOB_LOCATION IN(79,80)
and p.PBI_JOB_STATUS ='IN SERVICE'
and p.PBI_DEPARTMENT NOT IN('Admin (Support Service Section)','TRS','GYM')
and h.att_date BETWEEN '2018-08-26' AND '2018-09-25';
Delete h
FROM hrm_att_summary h,personnel_basic_info p
WHERE
h.emp_id=p.PBI_ID
and h.out_time<concat(h.att_date,' ',h.sch_out_time)
and h.sch_out_time>'06:00:00'
and h.leave_id=0
and h.iom_sl_no=0
and p.JOB_LOCATION IN(79,80)
and p.PBI_JOB_STATUS ='IN SERVICE'
and p.PBI_DEPARTMENT NOT IN('Admin (Support Service Section)','TRS','GYM')
and h.att_date BETWEEN '2018-08-26' AND '2018-09-25';
|