Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   Issue in getting max and min date from one field (http://www.go4expert.com/forums/issue-getting-max-min-date-field-t27342/)

newphpcoder 13Dec2011 10:59

Issue in getting max and min date from one field
 
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 28May2012 22:15

Re: Issue in getting max and min date from one field
 
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 3Jan2013 11:29

Re: Issue in getting max and min date from one field
 
Fields must be date type then only it will work.


All times are GMT +5.5. The time now is 17:49.