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.

haven’t we seen this problem before?

didn’t you already start like two or three threads on this same problem?

anyhow, to answer your question, obviously you need a different strategy than grouping by employee and date, since the time in and time out are different dates

what that strategy might be is difficult for me to say at this point, given how much effort you’ve put into this problem over the last few weeks (or months)

this is different thread or issue, because i only found it today, my previous thread i already resolved it and now i noticed that i have a problem in my 09:35 - 05:35 shift…

You mean should I need to group it my max_dtr and min_dtr?

Thank you

Is it possible to subtract one day???

When i tried to group EMP_NO, a.DTR, b.DTR, the output is still wrong :frowning:

I tried to research regarding my problem, and still I could not find a solution, but now I tried to try and test query in my mysql

Thank you

Now, i test query from testing data

here is my sample data:

EMP_NO ---- DATE_DTR ---- DTR
9300127 ---- 2011-11-15 — 2011-11-15 05:35:00
9300127 ---- 2011-11-15 — 2011-11-15 13:35:00
9300127 ---- 2011-11-16 — 2011-11-16 21:35:00
9300127 ---- 2011-11-17 — 2011-11-17 05:35:00

when I tried this code:


SELECT a.EMP_NO, a.DATE_DTR, 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; 

the output is:

EMP_NO ----- DATE_DTR ------ MAX(b.DTR) ---------------MIN(a.DTR)
9300127 ----- 2011-11-15 ---- 2011-11-17 05:35:00 ------2011-11-15 05:35:00
9300127 ----- 2011-11-16 ---- 2011-11-17 05:35:00 ------2011-11-16 21:35:00 //correct output for 09:35PM-05:35AM
9300127 ----- 2011-11-17 ---- 2011-11-16 21:35:00 ------2011-11-17 05:35:00

and I also tried this query:


SELECT a.EMP_NO, a.DATE_DTR, 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; 

and the output is:
EMP_NO ----- DATE_DTR ------ MAX(b.DTR) ---------------MIN(a.DTR)
9300127 ----- 2011-11-15 ---- 2011-11-15 13:35:00 ------2011-11-15 05:35:00 //correct
9300127 ----- 2011-11-16 ---- 2011-11-16 21:35:00 ------2011-11-16 21:35:00
9300127 ----- 2011-11-17 ---- 2011-11-17 05:35:00 ------2011-11-17 05:35:00

I tried to solved it now by try and error:(

Thank you so much

I have an idea to get the21:35:00 - 05:35:00 shfit but I dpn’t know how can I query it.

Like for example I have this data December 2, 2011 and December 3, 2011

00011 2011-12-02 05:35:00
00011 2011-12-02 13:35:00
00011 2011-12-02 13:35:00
00011 2011-12-02 21:35:00
00011 2011-12-02 21:35:00
00011 2011-12-03 05:35:00

I think I need to get the range from the previous date up to next date 05:35:00 to get the 21:35:00 - 05:35:00 shift.

Is it possible?And how??

I try to code it but i don’t know what syntax.

Thank you