1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Issue in getting max and min date from one field

Discussion in 'MySQL' started by newphpcoder, Dec 13, 2011.

  1. newphpcoder

    newphpcoder New Member

    Joined:
    Sep 24, 2010
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Web Programmer
    Location:
    Philippines
    Hi...
    I have a php code for importing attendance data to database.
    I have here the sample data that was imported in database:
    EMP_NO--------DATE_DTR----------DTR
    9300127--------2011-11-14---------2011-11-14 05:35:00
    9300127--------2011-11-14---------2011-11-14 13:35:00
    9300127--------2011-11-15---------2011-11-15 13:35:00
    9300127--------2011-11-15---------2011-11-15 21:35:00
    9300127--------2011-11-16---------2011-11-16 21:35:00
    9300127--------2011-11-17---------2011-11-17 05:35:00
    9300127--------2011-11-17---------2011-11-17 21:35:00
    9300127--------2011-11-18---------2011-11-18 05:35:00
    As you can see I have 3 different shifts:
    -- 05:35:00 - 13:35:00
    -- 13:35:00 - 21:35:00
    -- 21:35:00 - 05:35:00
    because of the time in and timeout is in one field, i used the mysql function max and min to get time in and time out and I inserted it in different tables.
    I used this code for inserting data with the time in and timeout is separately.
    Code:
    INSERT INTO regular_dtr_total(EMP_NO, DATE_DTR, max_dtr, min_dtr) 
    SELECT a.EMP_NO, a.DATE_DTR, 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;
    
    and the output of this code is:
    EMP_NO ---------- DATE_DTR--------- max_dtr--------------------min_dtr
    9300127-----------2011-11-14---------2011-11-14 13:35:00-----2011-11-14 05:35:00 // this is correct output because his shift is 05:35:00 - 13:35:00
    9300127-----------2011-11-15---------2011-11-15 21:35:00-----2011-11-15 13:35:00 // this is also correct output because his shift is 13:35:00 - 21:35:00
    9300127-----------2011-11-16---------2011-11-16 21:35:00-----2011-11-16 21:35:00 // wrong output
    9300127-----------2011-11-17---------2011-11-17 21:35:00-----2011-11-17 05:35:00 //wrong output
    9300127-----------2011-11-18---------2011-11-18 05:35:00-----2011-11-18 05:35:00 // wrong output

    the max_dtr = time out of employee
    the min_dtr = time in of employee
    I want output is like this:
    EMP_NO ---------- DATE_DTR--------- max_dtr--------------------min_dtr
    9300127-----------2011-11-14---------2011-11-14 13:35:00-----2011-11-14 05:35:00 // this is correct output because his shift is 05:35:00 - 13:35:00
    9300127-----------2011-11-15---------2011-11-15 21:35:00-----2011-11-15 13:35:00 // this is also correct output because his shift is 13:35:00 - 21:35:00
    9300127-----------2011-11-16---------2011-11-17 05:35:00-----2011-11-16 21:35:00 // because it is from shift 21:35:00 TO 05:35:00
    9300127-----------2011-11-17---------2011-11-18 05:35:00-----2011-11-17 21:35:00 // same with the above example with 21:35;00 - 05:35:00 shift.
    As you noticed i have only problem in using max and min to get the timein and timeout in the shift for 21:35:00 - 05:35:00 because they are diiferent date.
    I have no idea how can i resolved my problem in 21:35:00-05:35:00 shift. I don't know how can i revised my syntax that will not affect the two shifts that has a correct output.
    Any help is highly appreciated and I want you to know that the reason why I posted my problem it's because its urgent and I'm not good in logic in mysql query and also i'm not familiar with other functions of mysql..
    Again Thank you so much in advance.
    If you have any questions for further understanding of my problem feel free to ask me..
    Thank you
     
  2. pein87

    pein87 New Member

    Joined:
    Aug 6, 2010
    Messages:
    173
    Likes Received:
    47
    Trophy Points:
    0
    Occupation:
    Web Dev
    Location:
    Limbo
    Try using MIN() and MAX() or YEAR(), MONTH(), but the column type has to be a date type and not a string/char type.
     
  3. tvrameshmc

    tvrameshmc New Member

    Joined:
    Jan 3, 2013
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Fields must be date type then only it will work.
     

Share This Page