get the datetime range of rows from one column

Good day!

I have table attendance
fields are:

EMP_NO
DTR

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...

First IN and last OUT:


SELECT EMP_NO, MIN(DTR) AS FirstIn, MAX(DTR) AS LastOut
FROM attendance
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?

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.

Good day!

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;


table:test_att
columns: EMP_NO,DATE, ATTENDANCE

table: dtr_total
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