Change syntax from Update Case Statement to Update Union Statement

[newphpcoder]

Good day!

Theirs a programmer told me that could I used Union Statement to solve my issue in my query, but i can't imagine how can I do that, because my query I used case statement, I have no idea how can I used union instead of case statement.

Here is my problem, I need to get the rendered hours and the rendered hours should be only 8 hours or less 8 hours if the employee was time in late.

I have 3 shifts 21:35 - 05:35, 05:35- 13:35, 13:35-21:35

using case statement it works only in one shift, I want to get the rendered with different scheduled.

Like for example :
Employee 1 time in = 21:00 time out = 05:40 the rendered = 8 hours only event he time in early and time out late it only compute the 21:35 - 05:35
Employee 2 time in = 06:35 time out = 13:50 the rendered should be = 7 hours only because he was late 1 hour because his schedule is 05:35 but he time in 06:35 even he time out late its not considered because only time from 05:35-13:35 will get
Employee 3 time in = 13:35 time out = 24:35 the rendered should be = 8 only even he timeout late.

Rendered should be compute only the 8 hours of work, no matter he time out late only 8 hours would be get based on their schedule. and subtrace if he time in late.

here is my syntax in case statement:

UPDATE employee SET rendered = sec_to_time(time_to_sec('08:00:00') + 
            case 
              when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0
              else time_to_sec('21:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
            end; 

it is only for one shift and it works, but when I tried this to get the rendered for 3 shifts:

UPDATE employee SET rendered = sec_to_time(time_to_sec('08:00:00') + 
            case 
              when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0
              else time_to_sec('21:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
            end +
case 
              when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0
              else time_to_sec('05:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('13:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('13:35:00')
            end +
    case 
              when time_to_sec(time(timein)) < time_to_sec('13:35:00') then 0
              else time_to_sec('13:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('21:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('21:35:00')
            end
); 

wrong output was displayed.

If UNION Statement is the solution? How? Thank you

Is there any query can i used? I really need to solved this .

Any help is highly appreciated.

Thank you so much