Code:
UPDATE payroll.reg_att SET Rendered = case
when time_to_sec(time(TotalHours)) <= time_to_sec('02:00:00')
then sec_to_time(time_to_sec('00:00:00'))
when time_to_sec(time(LOGIN)) <= time_to_sec('05:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('13:35:00')
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) <= time_to_sec('07:00:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('16:00:00')
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) <= time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('16:00:00')
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) <= time_to_sec('13:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('21:35:00')
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGOUT)) < time_to_sec('05:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('21:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))))
when time_to_sec(time(LOGOUT)) <= time_to_sec('05:35:00') AND time_to_sec(time(LOGIN)) <= time_to_sec('21:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '21:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGOUT)) >= time_to_sec('05:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('21:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '21:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGOUT)) < time_to_sec('13:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('05:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) > time_to_sec('13:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('21:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))))
when time_to_sec(time(LOGOUT)) <= time_to_sec('21:35:00') AND time_to_sec(time(LOGIN)) <= time_to_sec('13:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGOUT)) < time_to_sec('21:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('13:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) > time_to_sec('05:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('13:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))))
when time_to_sec(time(LOGOUT)) < time_to_sec('21:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('13:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))))
when time_to_sec(time(LOGOUT)) < time_to_sec('13:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('05:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))))
when time_to_sec(time(LOGOUT)) <= time_to_sec('13:35:00') AND time_to_sec(time(LOGIN)) <= time_to_sec('05:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))) - time_to_sec('08:00:00'))
END;
I got a problem to get the rendered for 21:35:00 - 05:35:00 shift if the login <= 21:35:00 logout >= 05:35:00