newphpcoder Posted December 23, 2011 Share Posted December 23, 2011 Hi...I'm sorry if i posted again this issue..I just really want a help to resolved my problem...I just want to know what logic..what syntax should i need to used to satisfied the conditions that I needed so that the rendered will have a correct output. Because in rendered will depend the salary of an employee :(Honestly, I always think what syntax should i need but still I really don't know how to do it..I hope somebody will understand my situation and help me to solve it.Thank you so much...EMP_NO-------DATE_DTR-------LOGIN------------------------LOGOUT---------------RENDERED----------This shift is 21:35:00 - 05:35:0000300395-----2011-12-01-----2011-12-01 21:30:00----------2011-12-02 05:45:00--08:00:00//rendered should be 08:00:00 because his login <= 21:35:00 and his logout is >= 05:35:00But i have a lot of shift:21:35:00 - 05:35:0005:35:00 - 13:35:0013:35:00 - 21:35:0007:00:00 - 16:00:0008:00:00 - 16:00:0008:00:00 - 17:00:0008:00:00 - 18:00:00when I used this code: UPDATE payroll.reg_att SET Rendered = casewhen 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 when time_to_sec(time(LOGIN)) <= time_to_sec('21:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('05:35:00')then sec_to_time(time_to_sec('08:00:00')) when I used it all shifts will affected..Because I think it only check in time.I have a lot of conditions needed per shift.Like this://if the employee is early login from his shift and late logout from his shift the rendered will be 08:00:00//if the employee is late to login the 08:00:00 will minus//if the employee is undertime or early to logout 08:00:00 will minus//if the employee is late to login and early to logout the the sum of late login and early logout will minus in 08:00:00IF LOGIN <= 'SHIFT IN' AND LOGOUT >= 'SHIFT OUT' THEN RENDERED WILL 08:00:00IF LOGIN <= 'SHIFT IN' AND LOGOUT < 'SHIFT OUT' THEN RENDERED WILL 08:00:00 - (LOGOUT - SHIFT OUT)IF LOGIN > 'SHIFT IN' AND LOGOUT >= 'SHIFT OUT' THEN RENDERED WILL 08:00:00 - (LOGIN - SHIFT IN)IF LOGIN >= 'SHIFT IN' AND LOGOUT <= 'SHIFT OUT'THEN RENDERED WILL 08:00:00 + (LOGOUT - SHIFT) 08:00:00 - (LOGIN - SHIFT) Thank you so much... Link to comment Share on other sites More sharing options...
justsomeguy Posted December 23, 2011 Share Posted December 23, 2011 If you want to do all of that in SQL, I suggest you use a stored procedure. Otherwise, that logic should go in PHP. If you want to cram all of that logic into a single massive SQL statement then you'll make it pretty difficult for someone to make changes to that at a later time, it's not very easy to understand. Link to comment Share on other sites More sharing options...
davej Posted December 24, 2011 Share Posted December 24, 2011 I think you should create temporary tables and process the temporary tables one at a time. For example you might copy all of the entries from the master table which meet a certain criteria -- such as having login times between 05:35:01 and 07:00:00 on December 10 and create a temporary table and then process that table. Then drop the temporary table and create it again with the next group who logged in between 07:00:01 and 08:00:00 on December 10 and then process that table. Etc... Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.