View Full Version : get the datetime range of rows from one column
11-17-2011, 04:08 AM
I have table attendance
The DTR data has a data of In and Out of employee...
Like for example: Schedule 05:35:00 - 13:35:00
EMP_NO: 1001 DTR: 2011-10-24 05:35:10 //IN
EMP_NO: 1001 DTR: 2011-10-24 05:35:15 // IN
EMP_NO: 1001 DTR: 2011-10-24 13:35:00 // OUT
EMP_NO: 1001 DTR: 2011-10-24 13:40:20 // OUT
As you can see the employee twice IN and also he out twice. How can I get the first In and the last Out? And also how can I get the total hours of employee?
Thank you so much...
11-17-2011, 06:12 AM
First IN and last OUT:
SELECT EMP_NO, MIN(DTR) AS FirstIn, MAX(DTR) AS LastOut
GROUP BY EMP_NO
ORDER BY EMP_NO
What is your definition of "total hours" of an employee? Is it the time between FirstIn and LastOut? What is the point of the extra IN and OUT in there?
11-17-2011, 06:13 AM
I should note that if you have data for more than one day in the table, you will get the first in ON THE FIRST DAY and the last out ON THE LAST DAY.
That's surely not what you want.
If you do have data like that, say so.
11-17-2011, 08:07 AM
I resolved it using this code:
INSERT INTO dtr_total(EMP_NO, Total) SELECT a.EMP_NO, TIMEDIFF(max(b.ATTENDANCE), min(a.ATTENDANCE))
FROM test_att a
LEFT JOIN test_att b ON a.EMP_NO = b.EMP_NO;
columns: EMP_NO,DATE, ATTENDANCE
columns: EMP_NO, Total, Rendered
Now I don't know how can I get the rendered, the rendered is timedifference between IN and OUT but only get the difference between their schedule
like for example my shift is 05:35:00 - 13:35:00
my IN = 05:35:00 and OUT = 14:35:00 Total will be = 9 hours because he out late. and Rendered should be = 8 , no matter he out late the rendered will still 8 hours, unless he timeout early or timein late the rendered will be minus.
I have 3 shifts, 21:35:00- 05:35:00 which is night shift, and morning shift 05:35:00 - 13:35:00, 13:35:00 - 21:35:00
Thank you so much
Powered by vBulletin® Version 4.2.2 Copyright © 2017 vBulletin Solutions, Inc. All rights reserved.