...

View Full Version : query max and min with two situation, same and different date



newphpcoder
12-19-2011, 11:01 AM
Hi..


I know its a couple of weeks that i have problem in datetime..

For example i have this data:

00100 2011-11-20 05:35:00
00100 2011-11-20 13:35:00
00100 2011-11-21 21:35:00
00100 2011-11-22 05:35:00

when I tried this query for testing:



SELECT a.EMP_NO, max(a.DTR), min(b.DTR) FROM regular_dtr a LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO;


i have this output:

EMP_NO--max_dtr------------------min_dtr
00100----2011-11-22 05:35:00-----2011-11-20 05:35:00

i need result is:

EMP_NO--max_dtr------------------min_dtr
00100----2011-11-20 13:35:00-----2011-11-20 05:35:00
00100----2011-11-22 05:35:00-----2011-11-21 21:35:00

I really, don't know what syntax should i need..

I'm sorry if until now, I did not solve this:(

Any help is highly appreciated..

Thank you so much...i hope you would not angry with me...the reason why i post again this problem because now i only have two columns, EMP_NO and DTR(IN and OUT) and i have no right to demand to separate the data of in and out...so that in my part I need to do that but sad to say, I have only few knowledge in mysql..specially in functions.

Old Pedant
12-20-2011, 01:24 AM
*sigh*

I'm going to say this again. I do *NOT* think you are going to find a good MySQL solution to this problem.

As you already know, you can't do


SELECT EMP_NO, DATE(dtr) AS theDate, MIN(dtr) AS minDtr, MAX(dtr) AS maxDtr
FROM dtr
GROUP BY EMP_NO, DATE(dtr)

Because that won't allow you to have a shift that starts on one day and ends on the next.

I *STILL* say that the best way to solve this is to generate a *NEW TABLE*, probably using PHP code, that *DOES* group the start and end of each shift for each employee in one record.

*IF* you could *GUARANTEE* that there are *ZERO* extra records in the table--that is, if you can *guarantee* that no employee clocks in twice or clocks out twice in the same shift, then you *COULD* write a very, very complex MySQL query that could do this. But even then it would not be as efficient as creating a NEW TABLE with the data in better organization.

Really and truly, it would *NOT* be that hard to create the PHP program that I suggested to you in a prior thread.

Old Pedant
12-20-2011, 01:31 AM
I'll tell you what: If you can *GUARANTEE* that NONE of the following conditions will ever happen, then I think I can figure out a way to write this in MySQL:

(1) If there *ARE* multiple check-ins or check-outs by one employee for the same shift, we can *IGNORE* all such multiple occurrences if they are within one hour (or two hours, or a time you choose, within limits) of each other.

(2) No shift will ever be less than 4 hours (or a time you can choose, within limits).

(3) No shift will ever be more than 12 hours.

The "within limits" part means that the length of a shift needs to be MORE THAN TWICE the amount of time that we can ignore for multiple check-ins or check-outs.

So this data would be okay:


EMP_NO dtr
111111 2011-11-21 21:35:05
111111 2011-11-21 21:55:10
111111 2011-11-22 04:15:20
111111 2011-11-22 04:30:15

The second checkin is 20 minutes after the first and so will be ignored.

The first checout is 15 minutes before the second and so will be ignored.

So then the query would produce a result of


EMP_NO checkin checkout
111111 2011-11-21 21:35:05 2011-11-22 04:30:15


*CAN* YOU GUARANTEE those conditions???

newphpcoder
12-20-2011, 08:59 AM
*sigh*

I'm going to say this again. I do *NOT* think you are going to find a good MySQL solution to this problem.

As you already know, you can't do


SELECT EMP_NO, DATE(dtr) AS theDate, MIN(dtr) AS minDtr, MAX(dtr) AS maxDtr
FROM dtr
GROUP BY EMP_NO, DATE(dtr)

Because that won't allow you to have a shift that starts on one day and ends on the next.

I *STILL* say that the best way to solve this is to generate a *NEW TABLE*, probably using PHP code, that *DOES* group the start and end of each shift for each employee in one record.

*IF* you could *GUARANTEE* that there are *ZERO* extra records in the table--that is, if you can *guarantee* that no employee clocks in twice or clocks out twice in the same shift, then you *COULD* write a very, very complex MySQL query that could do this. But even then it would not be as efficient as creating a NEW TABLE with the data in better organization.

Really and truly, it would *NOT* be that hard to create the PHP program that I suggested to you in a prior thread.

I like your suggestion to use php code..but i don't how?

Thank you:(

newphpcoder
12-20-2011, 09:14 AM
this sample data:

00100 2011-11-20 05:35:00 //this is the time in for the first day
00100 2011-11-20 13:35:00 // this is the time out for the first day
00100 2011-11-21 21:35:00 //this is the time in for the second day
00100 2011-11-22 05:35:00 // this is the time out for the second day.

this is the situation:

I have 3 shifts
1. 05:35 - 13:35 // this is the same date like for example: 2011-11-20 05:35 2011-11-20 13:35
2. 13:35 - 21:35 //this is the same date like for example: 2011-11-20 13:35 2011-11-20 21:35
3. 21:35 - 05:35 // this is not same date like for example: 2011-11-21 21:35 2011-11-22 05:35


And now theirs a changes and problem..Now I need to insert in a new table that data but extracted:

like this:

EMP_NO--date_dtr----- max_dtr------------------min_dtr
00100----2011-11-20---2011-11-20 13:35:00-----2011-11-20 05:35:00
00100----2011-11-21---2011-11-22 05:35:00-----2011-11-21 21:35:00

date_dtr is date from min_dtr

min_dtr is time in
max_dtr is time out

the min and max function is work correctly if the date is the same but in my third shift its not work correctly becuase is not the same date.

I really...really don't know how to fix it...

Thank you so much for your help..

i know that I don't have a brilliant logic but i tried..

newphpcoder
12-20-2011, 09:20 AM
if php code is the solution can you give me an example???

I really don't know where to start..

Thank you so much..

Old Pedant
12-20-2011, 10:17 PM
I already did this. On 12 December. Over a week ago.

Look at my post #9 in this thread:
http://www.codingforums.com/showthread.php?t=246104

But ALSO read CAREFULLY what I wrote above.

I *THOUGHT* you told us in a prior post that SOMETIMES a person would checkin twice, by mistake, or sometimes would checkout twice.

IS THIS NO LONGER TRUE? Because if you will *NEVER* (and I do mean *NEVER*...absolutely never! not even one time in many thousands) have that situation--if there is *ALWAYS* only one checkin and checkout, then the problem gets simpler.

newphpcoder
12-21-2011, 01:36 AM
I already did this. On 12 December. Over a week ago.

Look at my post #9 in this thread:
http://www.codingforums.com/showthread.php?t=246104

But ALSO read CAREFULLY what I wrote above.

I *THOUGHT* you told us in a prior post that SOMETIMES a person would checkin twice, by mistake, or sometimes would checkout twice.

IS THIS NO LONGER TRUE? Because if you will *NEVER* (and I do mean *NEVER*...absolutely never! not even one time in many thousands) have that situation--if there is *ALWAYS* only one checkin and checkout, then the problem gets simpler.

Yes there a times that the employee checkin and check out twice. so i used min and max to get only the minimum and maximum time for check in and check out.


Thank you

newphpcoder
12-21-2011, 01:40 AM
this is your suggestion:



(1) Use the simple query
SELECT EMP_NO, DTR FROM regular_dtr ORDER BY EMP_NO, DTR

(2) Read one record. Presumably, it will be a CHECKIN DTR. Remember the DTR value from that records as the "checkin" time.
(3) In a loop, read the next records. When you find one that is obviously for the same SHIFT as the record from (2) you remember its DTR value as the "checkout" time. You may only find one record for the same SHIFT or you may find 2 or 3 more for that shift. [*You* will have to define what a "SHFIT" is. I would assume it is a checkout time that is no more than, say, 12 hours (?? maybe??) from the checkin.
(4) When you read a DTR time that obviously is *NOT* from the same SHIFT, then you write a record to the new table:
EMP_NO, CHECKIN_DTR, CHECKOUT_DTR
(5) After writing that record, you use the DTR time that is not from the same SHIFT as the new checkin time for the *next* SHIFT. And you loop back to (3).


the first step i run that query in mysql..

but i'm sorry i don't know how to do it the 2 - 5 steps.is it in php code?how?

I'm not good in looping..

Thank you so much

newphpcoder
12-21-2011, 01:45 AM
1. Actually, this is the scenario..i upload that data in my database. And I only used mysql insert statement. And after the data inserted in a table i have again the another table where inserted the data but separate the min and max of DTR. I only used min and max top distinguish what is the min or check in and max or check out.
2.Yes, it happens...with the used of min and max i only get the minimum time for check in and maximum time for check out.
3.the programs for attendance is separately...I only get the data from the database.
4. the program for attendance is 24 hours run..but I get only the data before the cut off period like for example i get the attendance from december 1, 2011 - december 15, 2011 I will get it on december 16, 2011 so that the data is completed.
5.the table contain all the history of attendance. for the reference.
6. I don't have programs to.. i only have upload programs to upload the attendance and i used insert statement to save the data in my database.

Old Pedant
12-21-2011, 02:08 AM
But MIN and MAX do *NOT* work, as you know, when the checkin and checkout are on different days.

Anyway, I don't code in PHP so I'm not going to tackle writing those steps 2 through 5 for you. They should not be hard at all for an experienced PHP coder. I know I could easily write them in ASP or JSP code.

I already said that *IF* you could guarantee the conditions I outlined in post #3 above, then I think this could be done in MySQL. So far you haven't responded at all to my post #3.

Old Pedant
12-21-2011, 02:12 AM
And you keep showing us the same examples over and over, and you keep telling us how the system works over and over. I really do think I understand the system. It's a really stupid system and whoever invented it should be fired. It that's your boss, then he should be fired.

But I still say the problem *CAN* be solved *IF* you can guarantee some limits and can write some code.

newphpcoder
12-21-2011, 03:14 AM
I'll tell you what: If you can *GUARANTEE* that NONE of the following conditions will ever happen, then I think I can figure out a way to write this in MySQL:

(1) If there *ARE* multiple check-ins or check-outs by one employee for the same shift, we can *IGNORE* all such multiple occurrences if they are within one hour (or two hours, or a time you choose, within limits) of each other.

(2) No shift will ever be less than 4 hours (or a time you can choose, within limits).

(3) No shift will ever be more than 12 hours.

The "within limits" part means that the length of a shift needs to be MORE THAN TWICE the amount of time that we can ignore for multiple check-ins or check-outs.

So this data would be okay:


EMP_NO dtr
111111 2011-11-21 21:35:05
111111 2011-11-21 21:55:10
111111 2011-11-22 04:15:20
111111 2011-11-22 04:30:15

The second checkin is 20 minutes after the first and so will be ignored.

The first checout is 15 minutes before the second and so will be ignored.

So then the query would produce a result of


EMP_NO checkin checkout
111111 2011-11-21 21:35:05 2011-11-22 04:30:15


*CAN* YOU GUARANTEE those conditions???

what would be the query for this??it is correct output...

I just want to know what is the query for that.

Thank you so much

robins5788
12-21-2011, 03:46 AM
what would be the query for this??it is correct output...

I just want to know what is the query for that.

Thank you so much

newphpcoder
12-21-2011, 04:08 AM
what would be the query for this??it is correct output...

I just want to know what is the query for that.

Thank you so much

I don't know what query he used for that..

but i tried this query:


SELECT a.EMP_NO, min(a.DTR), max(b.DTR) FROM regular_dtr a LEFT JOIN regular_dtr b ON (a.EMP_NO = b.EMP_NO) ORDER BY a.EMP_NO, a.DTR;


this is the data:
EMP_NO-------DTR-----
00100 --------2011-11-20 05:35:00 //this is the time in for the first day
00100 --------2011-11-20 13:35:00 // this is the time out for the first day
00100 --------2011-11-21 21:35:00 //this is the time in for the second day
00100 --------2011-11-22 05:35:00 // this is the time out for the second day.

but when i run this query the output is:

EMP_NO-------DATE----------min(a.DTR)-------------max(b.DTR)
00100---------2011-11-20----2011-11-20 05:35:00---2011-11-22 05:35:00

I don't know what would be the query if I want output is:

EMP_NO-------DATE----------min(a.DTR)-------------max(b.DTR)
00100---------2011-11-20----2011-11-20 05:35:00---2011-11-20 13:35:00
00100---------2011-11-21----2011-11-21 21:35:00---2011-11-22 05:35:00

Thank you

Old Pedant
12-21-2011, 05:20 AM
robins5788 is a SPAMMER. He was trying to post a URL with his advertising on it, but the forum software blocked the URL. Many SPAMMERS who can't read or write English (or can't do it well) simply copy prior posts and then add their own advertising. Luckily, this forum blocks most of the ads.

***********

I have said this many times, and I'll say it again: I don't think the doing this by way of a query in MySQL is going to work. I still think the best way to do this is to write some PHP (or C++ or Java or ... ) code that implements a semi-intelligent algorithm and creates a new table with the right data.

newphpcoder
12-21-2011, 06:51 AM
robins5788 is a SPAMMER. He was trying to post a URL with his advertising on it, but the forum software blocked the URL. Many SPAMMERS who can't read or write English (or can't do it well) simply copy prior posts and then add their own advertising. Luckily, this forum blocks most of the ads.

***********

I have said this many times, and I'll say it again: I don't think the doing this by way of a query in MySQL is going to work. I still think the best way to do this is to write some PHP (or C++ or Java or ... ) code that implements a semi-intelligent algorithm and creates a new table with the right data.

can you help me to code it in php?

Thank you

newphpcoder
12-21-2011, 09:44 AM
I only want to know is what can i do?what is the syntax if i only have data EMP_NO and Daily_Time_Record which mix check in and check out and also theirs a scenario that the employee check in twice or check out twice or sometimes no checkin or no check out.

It's hard for me to figured out how can I get the date in check in, and the check in and checkout of an employee. Like i've said before i used min and max and i found out I have problem if the shift or his Daily_Time_Record is 2011-11-21 21:35:00 - 2011-11-22 05:35:00 the date is different...I don't have problem if the schedule is 2011-11-20 05:35:00 - 2011-11-20 13:35:00

My head was crushing i don't know what to do..what the syntax is.. :-[

If it is php code or pure mysql and how..

Thank you for your help..

Old Pedant
12-21-2011, 11:53 PM
One more time, I do *NOT* use PHP. I could try writing it for you, but I am just as apt to make a mistake in PHP coding as you are to make a mistake in MySQL. If you were an experienced PHP coder, I wouldn't worry about it, as I'm sure you could then fix my basic errors. But you aren't, so I am afraid that if I make a tiny PHP mistake you wouldn't know what is wrong.

It would be much better if you could take my suggest solution steps to somebody who knows PHP and get them to help you figure out the PHP coding.

newphpcoder
12-22-2011, 01:48 AM
can you also code it in asp?

I have a knowledge in php but i have no idea how can I code it..

But if you code it in asp.. I could have an idea how to code it in php..

Thank you

newphpcoder
12-22-2011, 01:52 AM
is this your suggestion you said that need to code in php?

(1) Use the simple query
SELECT EMP_NO, DTR FROM regular_dtr ORDER BY EMP_NO, DTR

(2) Read one record. Presumably, it will be a CHECKIN DTR. Remember the DTR value from that records as the "checkin" time.
(3) In a loop, read the next records. When you find one that is obviously for the same SHIFT as the record from (2) you remember its DTR value as the "checkout" time. You may only find one record for the same SHIFT or you may find 2 or 3 more for that shift. [*You* will have to define what a "SHFIT" is. I would assume it is a checkout time that is no more than, say, 12 hours (?? maybe??) from the checkin.
(4) When you read a DTR time that obviously is *NOT* from the same SHIFT, then you write a record to the new table:
EMP_NO, CHECKIN_DTR, CHECKOUT_DTR
(5) After writing that record, you use the DTR time that is not from the same SHIFT as the new checkin time for the *next* SHIFT. And you loop back to (3).

Notice that if the EMP_NO changes, that is *automatically* a change of SHIFT.



EMP_NO DTR
110011 Dec 3, 2011, 8:35 AM
110011 Dec 3, 2011, 9:05 AM
110011 Dec 3, 2011, 5:20 PM
110011 Dec 4, 2011, 9:20 PM
110011 Dec 4, 2011, 9:50 PM
110011 Dec 5, 2011, 3:50 AM
110011 Dec 5, 2011, 4:05 AM
220022 Dec 3, 2011, 8:40 AM
...

Isn't it *OBVIOUS* when looking at those date/times that the following is true?



EMP_NO BEGINSHIFT ENDSHIFT
110011 Dec 3, 2011 8:35 AM Dec 3, 2011 5:20 PM
110011 Dec 4, 2011 9:20 PM Dec 5, 2011 4:05 AM
220022 Dec 3, 2011 8:40 AM ... etc. ...


So by making one run through the "raw" DTR data, you should be able to create a table with BEGINSHIFT and ENDSHIFT and then you can do *ALL* your computations (e.g, total time worked, etc.) from that new table.

Old Pedant
12-22-2011, 10:11 PM
Okay...I will *TRY* to show you the PHP way to do it.

As I said, be ready to find many mistakes in my PHP code.

This assumes you have a new table created something like this:


CREATE TABLE newtable (
EMP_NO int,
CHECKIN datetime,
CHECKOUT datetime );



*SOMETHING* like this:


<?php
... establish the db connection ...

$sql = "SELECT EMP_NO, UNIX_TIMESTAMP(DTR) AS DT FROM regular_dtr ORDER BY EMP_NO, DTR";

$result = mysql_query( $sql );
if ( ! $result ) {
die('Unable to execute query: ' . mysql_error());
}

$emp = -999; // impossible employee number
$dtr = "";
$checkin = 0;
$checkout = 0;

$hourSec = 60*60; // one hour, in seconds, for UnixTime

function addRow()
{
$sql = "INSERT INTO newtable ( EMP_NO, CHECKIN, CHECKOUT ) "
. " VALUES( $emp, FROM_UNIXTIME($checkin), FROM_UNIXTIME($checkout) )";
// comment out next line after things start working
echo "DEBUG: " . $sql . "<br/>\n";
mysql_query($sql) OR die("Unable to insert data: $sql");
}

while ($row = mysql_fetch_assoc($result))
{
$curemp = $row["EMP_NO"];
$curdtr = $row["DT"];

// if we changed employees, write out a record to the new table
if ( $emp != $curemp )
{
if ( $emp != -999 ) { addRow(); }
$emp = $curemp;
$checkin = 0;
$checkout = 0;
}

// if the current DTR is more than 12 hours since last checkout,
// assume this is a new date for this employee:
if ( $checkin != 0 && $checkout != 0 && ( ($curdtr - $checkout) > 12*$hourSec ) )
{
addRow();
$checkin = 0;
$checkout = 0;
}

// okay, now the logical checks for checkin and checkout times:

// if this is first time for this user, this date, assume it is checkin:
if ( $checkin == 0 )
{
$checkin = $curdtr;
} else if ( ($curdtr - $checkin) < 2*$hourSec ) {
// otherwise, if current dtr is less than 2 hours from checkin, IGNORE it
// do nothing
} else if ( $checkout == 0 ) {
// else if we don't have a checkout time yet, then assume this is checkout
$checkout = $curdtr;
} else if ( ($curdtr - $checkout) < 2*$hourSec ) {
// otherwise, if current dtr is less than 2 hours from checkout,
// throw away prior checkout value and use this one:
$checkout = $curdtr;
} else {
// oops...we have checkin and checkout, but the current DTR
// is not within 2 hours of either one...report an error
echo "ERROR: Unable to assign current DTR to either checkin or checkout<br/>";
// probably should dump out more information here, but I'm not sure
// how to format that to be readable
}

} // end of loop while there are rows in the result

// if we have valid final information, write it:
if ( $emp != -999 && $checkin != 0 && $checkout != 0 )
{
addRow();
}
echo "<hr/>Completed<hr/>\n";
?>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum