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.

[Old Pedant]

Well, I told you before this was a bad db design. And now you've proven it.

Sorry, but you are finally out of luck. This bad data design has finally given you a situation there is no good answer for.

on't I remember you said that sometimes a person would have two checkins or two checkouts for a single shift?

If so, we could invent all kinds of bad answers, but with your data the way it is, no answer is going to work with all data.

[newphpcoder]

i have no choice in my data...because it is from attendance system...so from one field of their attendance i get the max and min but I noticed that I got problem in 09:35 Pm - 05:35 AM

because 05:35 Am become min instead of max because it is time out..

[Old Pedant]

I just don't think you are going to be able to do this in SQL.

What you *need* to do is process that raw data and use it to create a *new* table that has employeeid, checkin, and checkout fields, only. And the checkin and checkout fields should be DATETIME fields.

I think if you took the "raw" data and simply did

SELECT EMP_NO, DTR FROM regular_dtr ORDER BY EMP_NO, DTR

Then you could use PHP or some outside logic to find *PAIRS* of VALID records and use them to build a new table with

EMP_NO, START_DTR, END_DTR

In this way, you could look at adjacent records and, if you found (say) 4 times for the same shift you could just throw out the middle 2 and keep only the first and last.

[newphpcoder]

Originally Posted by Old Pedant

I just don't think you are going to be able to do this in SQL.

What you *need* to do is process that raw data and use it to create a *new* table that has employeeid, checkin, and checkout fields, only. And the checkin and checkout fields should be DATETIME fields.

I think if you took the "raw" data and simply did

SELECT EMP_NO, DTR FROM regular_dtr ORDER BY EMP_NO, DTR

Then you could use PHP or some outside logic to find *PAIRS* of VALID records and use them to build a new table with

EMP_NO, START_DTR, END_DTR

In this way, you could look at adjacent records and, if you found (say) 4 times for the same shift you could just throw out the middle 2 and keep only the first and last.

You mean I don't need to used min and max?

I really don't need how does the logic..

because in 09:35 PM - 05:35 AM they are different date:confused:


Thank you

[newphpcoder]

I know its wrong query but i tried to remove max and min:

SELECT a.EMP_NO, a.DTR AS START_DTR, b.DTR AS END_DTR FROM regular_dtr a LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO;

and yet the output is wrong...

I really don't know how can I solved it..

Thank you for your help...

[Old Pedant]

No, I mean you need to write a *SPECIAL* processing program, in PHP or whatever is your coding language of choice, that will run through your regular_dtr table and throw away the unused data and create a *NEW* table of the form I recommended.

I don't code PHP or I'd do it for you. If you want it in ASP code I would do it.

[newphpcoder]

I used php ...

Can you tell me the logic?

Thank you

[Old Pedant]

Okay, as I recall, you said that sometimes people would check in (or check out??) more than once. So your goal is to match up the first time a person checks in each day with the last time they checkout, right? Any other DTR records you will just ignore, yes?

So it's pretty simple:

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

So let's make up a sample:

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.

[newphpcoder]

Actually, on my syntax, i don't need o check his shift I just got their min and max time to become their time in and time out, but as I've said I got the problem in using max and min in 09:35 - 05:35 shift with different dates..

Someone told me that should I check if waht his previous shift?and also i need to minus one 1 day.

But, honestly, i don't know how cn i revised my code:(

Thank you for your help..

[newphpcoder]

Honestly, when you see the DTR data, you never know what his in or out...because the in and out is in the same fields.

Thank you