problem in getting the max date and min date

newphpcoder

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.

newphpcoder

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

newphpcoder

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

johanafm

Why store first date, then datetime (you have things like: 2011-11-27 2011-11-27 21:02:39)

Anyway, if you store timein as datetime and timeout as datetime, you can get the time diff using built in functions, since the date will qualify 05:35 as coming after 21:35 on the night before...

newphpcoder

johanafm;10992792 wrote:
Why store first date, then datetime (you have things like: 2011-11-27 2011-11-27 21:02:39)

Anyway, if you store timein as datetime and timeout as datetime, you can get the time diff using built in functions, since the date will qualify 05:35 as coming after 21:35 on the night before...

You mean like this?

INSERT INTO regular_dtr_total(EMP_NO, DATE_DTR, max_dtr, min_dtr, TotalHours) 
SELECT a.EMP_NO, a.DATE_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

I have separate date like this:

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

Thank you

johanafm

Ah, now I see. I missed the part about time in and out being one row each.

While you can match these rows two by two, you'd have to start from the beginning, and if either a time in or time out would be missing, everything would get screwed up (matching time in against the next time in etc from that point on).

So what you should do is add a new column keeping track of which shift this is. Or, you could actually change your current date column into a datetime column and store the date along with the start time of that shift, both for time in and time out. Once you do this, a.DATE_DTR = b.DATE_DTR will make sense. As it is now, that part may actually join more than 2 rows together (which it does with your sample data).

Also note that your insert is trying to insert data into 5 columns, while you only have data for 3 columns.

newphpcoder

johanafm;10992843 wrote:
Ah, now I see. I missed the part about time in and out being one row each.

While you can match these rows two by two, you'd have to start from the beginning, and if either a time in or time out would be missing, everything would get screwed up (matching time in against the next time in etc from that point on).

So what you should do is add a new column keeping track of which shift this is. Or, you could actually change your current date column into a datetime column and store the date along with the start time of that shift, both for time in and time out. Once you do this, a.DATE_DTR = b.DATE_DTR will make sense. As it is now, that part may actually join more than 2 rows together (which it does with your sample data).

Also note that your insert is trying to insert data into 5 columns, while you only have data for 3 columns.

I'm sorry I did not understand this part:

While you can match these rows two by two, you'd have to start from the beginning, and if either a time in or time out would be missing, everything would get screwed up (matching time in against the next time in etc from that point on).

How can I know what their shift is???😕
if the data is only
EMP_NO, DATE, and DTR

you could actually change your current date column into a datetime column and store the date along with the start time of that shift, both for time in and time out.

You mean I will change the datatype of DATE Field FROM DATE to DATETIME?

I'm sorry I'm not totally understand this:
and store the date along with the start time of that shift, both for time in and time out.

because of start time of that shift??

Can you give me an example for further understanding?

yeah.. iT was inserted 5 columns it should be 3 only...

I really need to fix this..

Thank you so much for your effort to help me..I really appreciate it..

newphpcoder

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

johanafm

What I meant is that you had

employee	shift_startdate	shift_actual_datetime (in or out)

and you should instead have

employee	shift_startdateTIME	shift_actual_datetime (in or out)

Thus, changing from date to datetime in your original example data

00011	2011-12-02 [b]05:35:00[/b]	2011-12-02 05:35:00
00011	2011-12-02 [b]05:35:00[/b]	2011-12-02 13:35:00
00011	2011-12-02 [b]13:35:00[/b]	2011-12-02 13:35:00
00011	2011-12-02 [b]13:35:00[/b]	2011-12-02 21:35:00

Now, you can join the rows together two by two by joining the table against itself on the second column. This is exactly what you did before

a.DATE_DTR = b.DATE_DTR

But without the shift start time, you wouldn't get just two rows joined since you have several shifts per day.