2 replies to this topic

[newphpcoder]

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

[wim DC]

So this table has only 2 columns?
EMP_NO ---- DTR

DTR is a varchar that actually contains that "//IN" or "//OUT" ? If not, how are you going to know whether it's in or out by looking at a random record?

[newphpcoder]

I already 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