Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    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:

    Code:
    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    *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
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (12-20-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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:
    Code:
    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
    Code:
    EMP_NO  checkin              checkout
    111111  2011-11-21 21:35:05  2011-11-22 04:30:15
    *CAN* YOU GUARANTEE those conditions???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (12-21-2011)

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    *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
    Code:
    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:(

  • #5
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    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..

  • #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    if php code is the solution can you give me an example???

    I really don't know where to start..

    Thank you so much..

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    I already did this. On 12 December. Over a week ago.

    Look at my post #9 in this thread:
    problem in getting the max date and min date

    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (12-21-2011)

  • #8
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I already did this. On 12 December. Over a week ago.

    Look at my post #9 in this thread:
    problem in getting the max date and min date

    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

  • #9
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    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

  • #10
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    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.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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:
    Code:
    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
    Code:
    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

  • #14
    Banned
    Join Date
    Dec 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Last edited by ShaneC; 12-21-2011 at 04:21 AM. Reason: Removed spam

  • #15
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by robins5788 View Post
    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:
    Code:
    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
    Last edited by ShaneC; 12-21-2011 at 04:22 AM. Reason: Removed spam quote


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •