Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: 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. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I think you should try providing more information about what you are trying to achieve, some background will be more helpful in this case. For example, is this a checkin, checkout solution where the times provided indicate the checkin time and then the next value is the checkout time and you need to group these together?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    //this is the first group or attendance
    00100 2011-11-20 05:35:00 // check in
    00100 2011-11-20 13:35:00 // check out

    //this is the second attendance
    00100 2011-11-21 21:35:00 // check in
    00100 2011-11-22 05:35:00 // check out

    Thank you

  4. #4
    Join Date
    Dec 2010
    Posts
    136
    if php code is the solution can you give me an example???

    I really don't know where to start..

    Thank you so much..

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    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. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by newphpcoder View Post
    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..
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    As Rudy says, there is no easy solution even for experts in this area. You will also need to take a step backwards to make sure that the logic that you are implying matches the data that you actually have. For instance, what if someone checks in but no record exists for them checking out.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Dec 2010
    Posts
    136
    if he has no check out the check in and check out will be the same... and it's not a problem... the problem is if the date is not the same... the min and max is not capable for that... like if tha data is 2011-11-21 21:35:00 2011-11-22 05:35:00

    the correct time in is 2011-11-21 21:35:00
    the correct time out is 2011-11-22 05:35:00

    but when i used min and max the output is
    time in 2011-11-21 21:35:00
    time out 2011-11-21 21:35:00

    time in 2011-11-22 05:35:00
    time out 2011-11-22 05:35:00

    Thank you

  9. #9
    Join Date
    Dec 2010
    Posts
    136
    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.

  10. #10
    Join Date
    Dec 2010
    Posts
    136
    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.

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

  11. #11
    Join Date
    Dec 2010
    Posts
    136

    Problem in getting the attendance of employee based on their login and logout

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

    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:
    Code:
    UPDATE payroll.reg_att SET Rendered =  case
    when time_to_sec(time(TotalHours)) <= time_to_sec('02:00:00')
    then sec_to_time(time_to_sec('00: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('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('16: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('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'))) + 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'))) - 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'))) + 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'))) - time_to_sec('08:00: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'))) + 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'))) - 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(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(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'))) + 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'))) - time_to_sec('08:00: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'))) + 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'))) - time_to_sec('08: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'))))
    
    
    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(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'))) + 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'))) - time_to_sec('08:00: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

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

Posting Permissions

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