Reply to post Problem in calculating time difference from one column with datatype d

newphpcoder · #1 (**permalink**) 11-22-11, 23:02

Good day!

I got a problem in my query syntax to get the total hours per day based on their time in and time out...

Here is the scenario:

the employee sometimes they swipe their id twice to Time In or to Time OUT, so I used syntax max in timeout and min in time in.

Here is the sample DTR data from the database:
EMP_NO DATE DTR
300395 11/3/2011 11/3/11 5:35 AM
300395 11/3/2011 11/3/11 1:35 PM
300395 11/4/2011 11/4/11 5:35 AM
300395 11/4/2011 11/4/11 1:35 PM
300395 11/5/2011 11/5/11 5:35 AM
300395 11/5/2011 11/5/11 1:35 PM
300395 11/6/2011 11/6/11 5:35 AM
300395 11/6/2011 11/6/11 1:35 PM
300395 11/7/2011 11/7/11 5:35 AM
300395 11/7/2011 11/7/11 1:35 PM
300395 11/8/2011 11/8/11 5:35 PM
300395 11/8/2011 11/8/11 1:35 PM
300395 11/9/2011 11/9/11 5:35 PM
300395 11/9/2011 11/9/11 1:35 PM
300395 11/10/2011 11/10/11 5:35 AM
300395 11/10/2011 11/10/11 1:35 PM
300395 11/11/2011 11/11/11 5:35 AM
300395 11/11/2011 11/11/11 1:35 PM
300395 11/12/2011 11/12/11 5:35 AM
300395 11/12/2011 11/12/11 1:35 PM
300395 11/14/2011 11/14/11 5:35 AM
300395 11/14/2011 11/14/11 1:35 PM
300395 1/15/2011 11/15/11 5:35 AM
300395 11/15/2011 11/15/11 1:35 PM
9300127 11/3/2011 11/3/11 5:35 AM
9300127 11/3/2011 11/3/11 1:35 PM
9300127 11/4/2011 11/4/11 5:35 AM
9300127 11/4/2011 11/4/11 1:35 PM
9300127 11/5/2011 11/5/11 5:35 AM
9300127 11/5/2011 11/5/11 1:35 PM
9300127 11/6/2011 11/6/11 5:35 AM
9300127 11/6/2011 11/6/11 1:35 PM
9300127 11/7/2011 11/7/11 5:35 AM
9300127 11/7/2011 11/7/11 1:35 PM
9300127 11/8/2011 11/8/11 5:35 PM
9300127 11/8/2011 11/8/11 1:35 PM
9300127 11/9/2011 11/9/11 5:35 PM
9300127 11/9/2011 11/9/11 1:35 PM
9300127 11/10/2011 11/10/11 5:35 AM
9300127 11/10/2011 11/10/11 1:35 PM
9300127 11/11/2011 11/11/11 5:35 AM
9300127 11/11/2011 11/11/11 1:35 PM
9300127 11/12/2011 11/12/11 5:35 AM
9300127 11/12/2011 11/12/11 1:35 PM
9300127 11/14/2011 11/14/11 5:35 AM
9300127 11/14/2011 11/14/11 1:35 PM
9300127 1/15/2011 11/15/11 5:35 AM
9300127 11/15/2011 11/15/11 1:35 PM

I used this syntax to get the timedifference per day/employee:

Code:

INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, TIMEDIFF(max(b.DTR), min(a.DTR))
FROM regular_dtr a
LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO GROUP BY a.EMP_NO;

the result in this query is:
EMP_NO TotalHours
300395 296:00:00
9300127 296:00:00

I want output is:

EMP_NO TotalHours
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
300395 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00
9300127 08:00:00

I search in internet fot the right syntax, i tried time_to_sec, DATEDIFF, sec_to_time, but still wrong input, I post my problem in forum because I need to solved it. And I need help..

Thank you so much..

newphpcoder · #2 (**permalink**) 11-22-11, 23:03

here is my new code and the data output:

Code:

INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, TIMEDIFF(max(b.DTR), min(a.DTR))
FROM regular_dtr a
LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO WHERE b.DATE_DTR = a.DATE_DTR AND a.EMP_NO = b.EMP_NO GROUP BY a.DATE_DTR,  a.EMP_NO;

DTR DATA:

Quote:

EMP_NO DATE DTR
300395 11/3/2011 11/3/11 5:35 AM
300395 11/3/2011 11/3/11 1:35 PM
300395 11/4/2011 11/4/11 5:35 AM
300395 11/4/2011 11/4/11 1:35 PM
300395 11/5/2011 11/5/11 5:35 AM
300395 11/5/2011 11/5/11 1:35 PM
300395 11/6/2011 11/6/11 5:35 AM
300395 11/6/2011 11/6/11 1:35 PM
300395 11/7/2011 11/7/11 5:35 AM
300395 11/7/2011 11/7/11 1:35 PM
300395 11/8/2011 11/8/11 5:35 AM
300395 11/8/2011 11/8/11 1:35 PM
300395 11/9/2011 11/9/11 5:35 AM
300395 11/9/2011 11/9/11 1:35 PM
300395 11/10/2011 11/10/11 5:35 AM
300395 11/10/2011 11/10/11 1:35 PM
300395 11/11/2011 11/11/11 5:35 AM
300395 11/11/2011 11/11/11 1:35 PM
300395 11/12/2011 11/12/11 5:35 AM
300395 11/12/2011 11/12/11 1:35 PM
300395 11/14/2011 11/14/11 5:35 AM
300395 11/14/2011 11/14/11 1:35 PM
300395 1/15/2011 11/15/11 5:35 AM
300395 11/15/2011 11/15/11 1:35 PM
9300127 11/3/2011 11/3/11 5:35 AM
9300127 11/3/2011 11/3/11 1:35 PM
9300127 11/4/2011 11/4/11 5:35 AM
9300127 11/4/2011 11/4/11 1:35 PM
9300127 11/5/2011 11/5/11 5:35 AM
9300127 11/5/2011 11/5/11 1:35 PM
9300127 11/6/2011 11/6/11 5:35 AM
9300127 11/6/2011 11/6/11 1:35 PM
9300127 11/7/2011 11/7/11 5:35 AM
9300127 11/7/2011 11/7/11 1:35 PM
9300127 11/8/2011 11/8/11 5:35 AM
9300127 11/8/2011 11/8/11 1:35 PM
9300127 11/9/2011 11/9/11 5:35 AM
9300127 11/9/2011 11/9/11 1:35 PM
9300127 11/10/2011 11/10/11 5:35 AM
9300127 11/10/2011 11/10/11 1:35 PM
9300127 11/11/2011 11/11/11 5:35 AM
9300127 11/11/2011 11/11/11 1:35 PM
9300127 11/12/2011 11/12/11 5:35 AM
9300127 11/12/2011 11/12/11 1:35 PM
9300127 11/14/2011 11/14/11 5:35 AM
9300127 11/14/2011 11/14/11 1:35 PM
9300127 1/15/2011 11/15/11 5:35 AM
9300127 11/15/2011 11/15/11 1:35 PM

I have 12days for 300395 and also 12 days for 9300127

And data out in database using this new query is:

300395 00:00:00
9300127 00:00:00
300395 08:00:00
9300127 08:00:00 // 11 output like this, it should be 12 output like this and no output 00:00:00
300395 00:00:00
9300127 00:00:00

total output is 26 rows

it works, but a little bit problem, because, I extra data with 00:00:00 output and also two output become 00:00:00 but it should 08:00:00.

Actually, the output will be 24 rows only, but it becomes 26 rows and the 4 rows has 00:00:00 output.

thank you so much

r937 · #3 (**permalink**) 11-23-11, 06:07

nope, sorry, not going to help you over here either

you've been struggling with this problem for a month, and have made no progress

the stuff we've tried to teach you, you seem incapable of grasping

seriously, ask your boss to get you some professional help because this sql is too difficult for you

newphpcoder · #4 (**permalink**) 11-24-11, 01:40

Now i resolved my problem in total hours per day...

And now my big problem I need to face is the rendered....Getting only the time between their shifts like 21:35:00 - 05:35:00, 05:35:00 - 13:35:00, and 13:35:00 - 21:35:00...

Rendered should be if he time in late it will be deduct on his time based on his schedule also if he timeout early...

I used case statement in my update query but it did not work...I really don't know what should I used syntax to solved my old and new problem.. the rendered:(

Sorry if until today I did not resolved it...

Thank you for your help...

Internet Services

Web Development

Digital Marketing

Consumer Tech