Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.

# Thread: get the datetime range of rows from one column

1. ## 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:

Code:
```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?

• ## Users who have thanked Old Pedant for this post:

newphpcoder (11-21-2011)

• 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:

PHP 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

•

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•