SELECT
	hr_att_time.ShiftNo AS shift,
hr_att_time.schedule_outtime,
	DATE(hr_att_time.schedule_outtime) AS schedule_out_date,
	CONVERT (
		IF (
			TRUNCATE (
				TIME_TO_SEC(
					TIMEDIFF(hr_att_time.OutTime, hr_att_time.schedule_outtime)
				) / 3600,
				2
			) >= 1,
			CONCAT(
				FLOOR(
					TRUNCATE (
						TIME_TO_SEC(
							TIMEDIFF(hr_att_time.OutTime, hr_att_time.schedule_outtime)
						) / 3600,
						2
					)
				),
				'.',
				IF (
					(
						TRUNCATE (
							TIME_TO_SEC(
								TIMEDIFF(hr_att_time.OutTime, hr_att_time.schedule_outtime)
							) / 3600,
							2
						) - FLOOR(
							TRUNCATE (
								TIME_TO_SEC(
									TIMEDIFF(hr_att_time.OutTime, hr_att_time.schedule_outtime)
								) / 3600,
								2
							)
						)
					) > 0.5,
					5,
					0
				)
			),
			0
		),
		DECIMAL(5,2)
	) AS overtime
FROM
	hr_att_time
LEFT JOIN hr_atd_holidays 
	ON hr_att_time.Date = hr_atd_holidays.holyday_date
WHERE
	hr_att_time.EmployeeID = '10'
	AND hr_att_time.Date BETWEEN '2025-07-01' AND '2025-07-31'
	AND hr_att_time.OutTime IS NOT NULL
	AND hr_atd_holidays.holyday_date IS NULL
ORDER BY
	hr_att_time.Date