Is there any snytax/query except case statement if impossible to get the rendered from timein and timeout correctly?
The case statement that I post is produce correct data in oly one shift, the rest is wrong.
Thank you
I tried this query:
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))
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))
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('05:35:00') then 0
else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
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')
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)
;
and i got this error:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'when time_to_sec(time(timein)) < time_to_sec(‘05:35:00’) then 0
e' at line 9
(0 ms taken)
You can’t have multiple ELSE statements in a CASE. You can have multiple WHEN’s, and only one ELSE (has to be the last one in the CASE).
By the way, since you have three shifts, how do you decide against which shift you have to confront the logged in and out times of the employee?
I only based on timein and timeout…Is it possible in case statement?How can I revise my case statement?
Thank you so much…I really need this to solve…Thank you
Is there any other syntax to solve my problem?or should i edit my case statement, but i am new in case statement…Thank you…
You mean the separate sql statement…?
This case statement is for only one shift;
select 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) FROM employee;
and now I want to add case for the shift 05:35:00 - 13:35:00 and 13:35:00 - 21:35:00. I really need this to solve, and I don’t have idea what syntax should i need…
Thank you
What rule do you use to decide what shift it is?
Honestly, i don’t have any idea on what rule should I use, I only want thing is get only the hours from their shift, when I test that case I’m so happy because it works, but when I add the two shift It did not work, only in one shift.
I’m not asking for SQL or PHP code, I’m asking for the logic. When does a checkin time belong to one shift, and when to another? You are the one that should know, it’s your application 
Is it: the shift start time that is closesed by the checkin time?
Anyway, I repeat that this is going to be extremely complicated to implement in a query (IMO). You really should implement this kind of logic in PHP (or whatever language you use).
I have 3 shifts…
21:35:00 - 05:35:00
05:35:00 - 13:35:00
13:35:00 - 21:35:00
I want is only the time from shift will read or get. like for example:
21:00:00 - 06:00:00 he timein early and timeout late i want the result will be 8:00:00 and if the employee late like:
06:00:00 - 02:00:00 i want the result will be the 8 hours will be subtracted because he is late.
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
so go back to that programmer and ask how
He did not reply when i ask how? 
what do you think?
you only have three shifts, right?
will the shifts ever change?
if so, you probably need a shifts table
otherwise you can just use a TINYINT and call the shifts 1, 2, 3
I merged your new thread with the old one, since you’re still working on the same problem.
A question:
If someone checks in at 18:35 (yes I know, will never happen, but it’s an example), is he 5 hours late (13.35-21.35 shift) or is he 3 hours early (21.35-05.35 shift) ?
How do you make that decision?
if his schedule is 13:35 - 21:35 he is late 5 hours if his time in is 18:35
Thank you
where is the information about which shift someone is on?
As of now, I only base on their Time in and time out, but I could add shift field if could help in my problem.
Thank you
it could help, yeah
a lot
Yes, thisa code work:
UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') +
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
);
Where could i add where clause?
I’m sorry, I did not understand…
Can you give me example?
Thankyou