Issue in getting max and min date from one field

newphpcoder's Avatar, Join Date: Sep 2010
Ambitious contributor
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
pein87's Avatar
Ambitious contributor
Try using MIN() and MAX() or YEAR(), MONTH(), but the column type has to be a date type and not a string/char type.
tvrameshmc's Avatar, Join Date: Jan 2013
Newbie Member
Fields must be date type then only it will work.