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:
PHP Code:
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:
PHP Code:
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
dBforums
>
Change syntax from Update Case Statement to Update Union Statement
11-10-11, 20:32
Linear Mode
