problem in getting the max date and min date

Hi...

I got an problem in my attendance for the shift of 09:35 PM - 05:35 AM

I have this example data that I was inserted in my database:

--09:35 PM - 05:35 AM Shift----

EMP_NO DATE_DTR DTR
00300395 2011-11-27 2011-11-27 21:02:39
00300395 2011-11-28 2011-11-28 05:36:48

---05:35 AM - 02:35 PM---

EMP_NO DATE_DTR DTR
00300395 2011-11-21 2011-11-21 05:09:09
00300395 2011-11-21 2011-11-21 13:39:35

---02:35 PM - 09:35 PM

EMP_NO DATE_DTR DTR
00300395 2011-11-15 2011-11-15 13:15:08
00300395 2011-11-15 2011-11-15 21:38:23

This sample data from three shifts and i got problem in 09:35 PM - 05:35 PM
here is my code to insert it in my database:


$sql = "INSERT INTO regular_dtr (EMP_NO, DATE_DTR, DTR) VALUES ('$EMP_NO', '$Date', '$DTR')";

As you noticed the TimeIn and TimeOut of employee is in one field.

And now i have another insert statement to get the min and max date of employee for time in and timeout.
And i noticed that I have problem in my 09:35 PM - 05:35 AM


 $result = mysql_query("INSERT INTO regular_dtr_total(EMP_NO, DATE_DTR, max_dtr, min_dtr, TotalHours) 

SELECT a.EMP_NO, a.DATE_DTR, max(b.DTR),min(a.dtr),

TIMEDIFF(max(b.DTR), min(a.DTR))

FROM regular_dtr a

LEFT JOIN regular_dtr b ON (a.EMP_NO = b.EMP_NO AND a.DATE_DTR = b.DATE_DTR)

GROUP BY a.EMP_NO, a.DATE_DTR") 

 or die(mysql_error());

It works in my 05:35 AM -02:35 PM and 02:35PM - 09:35 PM because in this shift is same in date, but in 09:35PM - 05:35 PM they are different date..

min_dtr = time in
max_dtr = time out

And the result of this insert query is like this:

-----09:35 PM - 05:35 AM ---

EMP_NO DATE_DTR max_dtr min_dtr
00300395 2011-11-27 2011-11-27 21:02:39 2011-11-27 21:02:39
00300395 2011-11-28 2011-11-28 21:08:35 2011-11-28 05:35:48

it shoud be like this:

EMP_NO DATE_DTR max_dtr min_dtr
00300395 2011-11-27 2011-11-28 05:35:48 2011-11-27 21:02:39

As you notices this date 2011-11-28 21:08:35 should be the time in for the date of 2011-11-28

And here is the correct output for 05:35 AM - 02:35 PM and 02:35 PM - 09:35 PM

EMP_NO DATE_DTR max_dtr min_dtr
00300395 2011-11-15 2011-11-15 21:38:23 2011-11-15 13:15:06 // 02:35 Pm - 09:35 PM
00300395 2011-11-21 2011-11-21 13:39:35 2011-11-28 05:09:09 // 05:35 AM - 02:35 PM

I hope somebody can help me to fix this problem..

And also i will find the solution for that.

Thank you so much..

Any help is highly appreciated and any question is free to ask for further understanding.