Query max and min with two situation, same and different date

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.

The question is, as always: what is the logic? Forget about the query for a moment. Describe in words what you want to achieve. Why do those date-times have to be paired like that?

guido, you know what they are, you’ve seen this problem several times before

they are punch in and punch out times, and he’s trying to calculate the overlap or shortfall between the actual in and out times and the scheduled in and out times

remember he once wanted to add a “shift” column which would indicate morning, afternoon, and evening shifts? each shift has different start and stop times

this is still the same probelm after so many weeks…

Ah yes, he confused me by putting the ‘out’ time in front of the ‘in’ time. So I thought he was fighting with some new requirement. And in a way he is, it seems every time he has less data in his tables to work with :shifty:

newphpcoder, all I can say is, do it in your PHP code.
You don’t know what shift the person is working. You don’t even know which is the ‘in’ and which is the ‘out’. And with ‘you’ I don’t mean you as a person, I mean a neutral observer looking at the data. For me, he could be working this shift (yeah I know, it’s a bit long):
00100 2011-11-20 13:35:00
00100 2011-11-21 21:35:00

i don’t know how can I do that in php…

Thank you for your help

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…

if php code is the solution can you give me an example???

I really don’t know where to start…

Thank you so much…

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.

  1. I know that 00100 2011-11-20 05:35:00 is the first time of the day, but how can a program know? Because it’s the first entry in the table?
  2. What happens if a person checks in twice by mistake? Is that possible?
  3. When the program runs, is it possible that a person is still working, so there’s only a check in time? For example:

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
==> the program runs a midnight, so the check out time for the second day isn’t present because the person is still working his shift.

  1. How often will this program run?
  2. And the table will contain only new data? In other words, will the data be eliminated from this table once it has been elaborated? Or does it contain the whole history and the program has to reelaborate everything each time it runs?

do you remember about a month ago i advised you to ask your boss to get you some help?

that advice still stands

you need someone to sit down with you and work with you for several hours, perhaps several days

i don’t believe you are capable of handling this probelm yourself

continuing to beg for help on various forums (you have the same thread going in at least three different forum web sites) is ultimately ~not~ going to get you anywhere

please, go speak to your boss

  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.
  2. 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.
  3. 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.

Thank you

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…
:confused:
Thank you for your help…

Someone give me this idea but my problem I need to code it in php but I have no idea how cn I code it 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.

Because this has become a PHP question, I’ve moved it to the PHP forum.

Can you post the PHP code you’ve come up with so far?

snorts awake Huh? wha? 5 more minutes.
Yes. Code. code is good.

And I want to interject something here.

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

Not to a computer, no it isnt. For that matter, its not even obvious to me.

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

Did the user check in at 9:20 or 9:50? did he leave at 3:50 or 4:05? I dont know.

Lets say your shifts are as you say

  1. 05:35 - 13:35
  2. 13:35 - 21:35
  3. 21:35 - 05:35

User checks in at 05:20 and checks out at 13:50.
What shift did he work? Cause he’s got hours in all 3.
What if he checked in at 17:35 and checked out at 01:35?

Hi…

I’m sorry if i posted again this issue…

I just really want a help to resolved my problem…

I just want to know what logic…what syntax should i need to used to satisfied the conditions that I needed so that the rendered will have a correct output.
Because in rendered will depend the salary of an employee :frowning:

Honestly, I always think what syntax should i need but still I really don’t know how to do it…

I hope somebody will understand my situation and help me to solve it.

Thank you so much…

EMP_NO-------DATE_DTR-------LOGIN------------------------LOGOUT---------------RENDERED-------
—This shift is 21:35:00 - 05:35:00
00300395-----2011-12-01-----2011-12-01 21:30:00----------2011-12-02 05:45:00–08:00:00

//rendered should be 08:00:00 because his login <= 21:35:00 and his logout is >= 05:35:00

But i have a lot of shift:
21:35:00 - 05:35:00
05:35:00 - 13:35:00
13:35:00 - 21:35:00
07:00:00 - 16:00:00
08:00:00 - 16:00:00
08:00:00 - 17:00:00
08:00:00 - 18:00:00

when I used this code:


UPDATE payroll.reg_att SET Rendered =  case
when time_to_sec(time(LOGIN)) <= time_to_sec('05:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('13:35:00') 
then sec_to_time(time_to_sec('08:00:00'))

when time_to_sec(time(LOGIN)) <= time_to_sec('07:00:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('16:00:00') 
then sec_to_time(time_to_sec('08:00:00'))

when time_to_sec(time(LOGIN)) <= time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('18:00:00') 
then sec_to_time(time_to_sec('08:00:00'))

when time_to_sec(time(LOGIN)) <= time_to_sec('13:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('21:35:00') 
then sec_to_time(time_to_sec('08:00:00'))

when time_to_sec(time(LOGOUT)) < time_to_sec('05:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('21:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))))

when time_to_sec(time(LOGOUT)) < time_to_sec('21:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('13:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))))

when time_to_sec(time(LOGOUT)) < time_to_sec('13:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('05:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))))

when time_to_sec(time(LOGIN)) > time_to_sec('21:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('05:35:00') 
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '21:35:00'))))

when time_to_sec(time(LOGIN)) > time_to_sec('13:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('21:35:00') 
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))))

when time_to_sec(time(LOGIN)) > time_to_sec('07:00:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('16:00:00') 
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '07:00:00'))))

when time_to_sec(time(LOGIN)) > time_to_sec('05:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('13:35:00') 
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))))
END;

I got a problem to get the rendered for 21:35:00 - 05:35:00 shift if the login <= 21:35:00 logout >= 05:35:00


when time_to_sec(time(LOGIN)) <= time_to_sec('21:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('05:35:00') 
then sec_to_time(time_to_sec('08:00:00'))

when I used it all shifts will affected…Because I think it only check in time.

I have a lot of conditions needed per shift.

Like this:

//if the employee is early login from his shift and late logout from his shift the rendered will be 08:00:00
//if the employee is late to login the 08:00:00 will minus
//if the employee is undertime or early to logout 08:00:00 will minus
//if the employee is late to login and early to logout the the sum of late login and early logout will minus in 08:00:00

IF LOGIN <= ‘SHIFT IN’ AND LOGOUT >= ‘SHIFT OUT’ THEN RENDERED WILL 08:00:00
IF LOGIN <= ‘SHIFT IN’ AND LOGOUT < ‘SHIFT OUT’ THEN RENDERED WILL 08:00:00 - (LOGOUT - SHIFT OUT)
IF LOGIN > ‘SHIFT IN’ AND LOGOUT >= ‘SHIFT OUT’ THEN RENDERED WILL 08:00:00 - (LOGIN - SHIFT IN)
IF LOGIN >= ‘SHIFT IN’ AND LOGOUT <= 'SHIFT OUT’THEN RENDERED WILL 08:00:00 + (LOGOUT - SHIFT) 08:00:00 - (LOGIN - SHIFT)

Thank you so much…