Go4Expert (http://www.go4expert.com/)
-   Database (http://www.go4expert.com/forums/database-forum/)
-   -   query max and min with two situation, same and different date (http://www.go4expert.com/forums/query-max-min-situation-date-t27405/)

newphpcoder 19Dec2011 15:43

query max and min with two situation, same and different date

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:

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:
00100----2011-11-22 05:35:00-----2011-11-20 05:35:00
i need result is:
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.

All times are GMT +5.5. The time now is 14:19.