If adding shift field in my database is one of the solution, what would be the datatype of shift field? Should I add Shift In and Shift Out Field or combine?
Thank you so much
Actually theres another shift 08:00 - 17:00, yes the four shift did not change.
So now, that I have fields for shift, how can we get the rendered?
Thank you so much for your help
Yes, now i add field for shift and I use:
data 1 for 21:35 - 05:35
data 2 for 05:35 - 13:35
data 3 for 13:35 - 21:35
data 4 for 08:00 - 08:00
and now, how can I used shift field to get the rendered?
Thank you
in post #31, you have an UPDATE statement and you said “it is only for one shift and it works”
add the appropriate WHERE clause to this statement
then write two (or three) more UPDATE statements for the other shifts
I tried to figured out what you say, but I don’t know how can I add where statement in case statement? Sorry I am new in case statement, and actually this code is only suggested by other.
Thank you
Yes it was suggested by me 
You don’t add the WHERE clause to the CASE, you add it to the UPDATE statement. Check the syntax here: http://dev.mysql.com/doc/refman/5.5/en/update.html
I used this code:
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 shift = 2;
UPDATE attendance 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
) WHERE shift = 1;
UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') +
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
) WHERE shift = 3;
UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') +
case
when time_to_sec(time(TimeIn)) < time_to_sec('08:00:00') then 0
else time_to_sec('08:00:00') - time_to_sec(time(TimeIn))
end +
case
when time_to_sec(time(TimeOut)) > time_to_sec('17:00:00') then 0
else time_to_sec(time(TimeOut)) - time_to_sec('17:00:00')
end
) WHERE shift = 4;
Is it what you suggest?
Thank you so much
that depends
did it work?
Yes, it works…is it what you suggest?
Good day!
My boss told me that I no need to add field for shift, but I got a problem in my code for the shift 21:35-05:35 and also in 13:35-21:35 .
here is the code:
$result = mysql_query("UPDATE attendance 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
)")
or die(mysql_error());
$result = mysql_query("UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') +
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
)")
or die(mysql_error());
i have time in : 2011-10-24 21:35:00 time out: 2011-10-25 08:35:00
and the ouput in rendered using this code is: -14:00:00 it should be 08:00:00
timein : 2011-10-24 13:35:00 timeout: 2011-10-24 21:35:00 rendered: 02:25:00 it should be 08:00:00
Help me…Thank you so much
I can’t. Ask your boss. If he knows you can do it without that new field, then let him tell you how to do it.
I toldl him, but he told me i don’t need to add shift because every week employment change their shift
I understand he said you don’t need to add shift. But ask him how to do the query without shift.
he really don’t know because he has no knowledge about it:(
I really don’t know how to solve it, its urgent:(
Ok, so he tells you you don’t need the shift, but he really has no knowledge about it. Sounds familiar 
If you have to do it without the shift column, you have to decide the limits of the check in times.
For example:
- if an employee checks in between 10:35:01 and 17:35:00 then he’s working the 13:35 - 21:35 shift
- if an employee checks in between 17:35:01 and 01:35:00 then he’s working the 21:35 - 05:35 shift
- if an employee checks in between 01:35:01 and 07:00:00 then he’s working the 05:35 - 13:35 shift
- if an employee checks in between 07:00:01 and 10:35:00 then he’s working the 08:00 - 17:00 shift
Once you’ve decided those limits, then you can implement them in the update query.
Where i can put the limit?Sorry. I’m not familiar with that…
Thank you so much…
by limits, he was referring to the times within which it’s obviously one shift and not another
read his post again
10:35:01 and 17:35:00 would be examples of the limits for the 13:35 - 21:35 shift
these limits are your responsibility to decide
can you give an example coding for that?is it in query?
sorry, i really don’t know how can I code it.
Thank you so much
come on, try