Problem in calculating time difference from one column with datatype datetime

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:


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

The 100+ responses in two threads in another forum not enough to get you started?

The 100+ responses in two threads in another forum not enough to get you started?

Sorry, I really don't know how can I solved my problem. that's why I post a lot of threads.

Yes, but when you post in multiple forums and people find out you are doing it, then you stop getting answers. It is called CROSS POSTING and it is very very bad "Netiquette."

I, for one, will not answer a question in *EITHER* forum if I find out it is cross posted.

Your problem here is a very easy one. It's hard for me to believe that it needed more then ONE answer. Why did you get hundreds?

By the way, the output you say you want is *USELESS*.

Say you *did* get output that looked like this:

EMP_NO TotalHours
300395 08:00:00
300395 07:00:00
300395 08:00:00
300395 07:00:00
300395 08:00:00
300395 05:00:00
300395 08:00:00
300395 03:30:00
300395 01:10:00
300395 08:00:00
300395 08:00:00
300395 04:12:00

HOW CAN YOU TELL which date goes with WHICH number of hours?????

What is the point of getting data that is as useless as that?

Sorry :(

I follow only what the instruction by my boss..

I post a lot of threads because its an urgent..

Thank you

yes but that is causing other people (whom you are not paying) to start from scratch when you have been given a lot of guidance already on the subject.

And we can't help it if you boss is an idiot. Or if you don't understand what he is saying, whichever it is.

I don't pretend to understand why you are inserting this data into another table. That's silly. Just create a VIEW that you can use over and over.


CREATE VIEW timeWorkedByEmployeeByDate
AS
SELECT EMP_NO, `DATE`, TIMEDIFF( MAX(DTR), MIN(DTR))
FROM regular_dtr
GROUP BY EMP_NO, `DATE`
ORDER BY EMP_NO, `DATE`

Presto.

Go on, try it.

Then you can use [icode]SELECT * FROM timeWorkedByEmployeeByDate[/code] any place you would have use the silly regular_dtr_total before.

And we can't help it if you boss is an idiot. Or if you don't understand what he is saying, whichever it is.

I don't pretend to understand why you are inserting this data into another table. That's silly. Just create a VIEW that you can use over and over.


CREATE VIEW timeWorkedByEmployeeByDate
AS
SELECT EMP_NO, `DATE`, TIMEDIFF( MAX(DTR), MIN(DTR))
FROM regular_dtr
GROUP BY EMP_NO, `DATE`
ORDER BY EMP_NO, `DATE`

Presto.

Go on, try it.

Then you can use [icode]SELECT * FROM timeWorkedByEmployeeByDate[/code] any place you would have use the silly regular_dtr_total before.

Okay..

I will try it..

Thank you so much...

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