sniperscope Posted April 18, 2013 Share Posted April 18, 2013 Hello i have a list in my database and i want to list them order by date and hour. Unfortunately my following sql query brings me wrong value. I need that display staff_id who works today and after 5pm and all from tomorrow. Example output should be like: |_Id_|___date___|__hour__| | 2 | 2013/4/18 | 22:00 | | 5 | 2013/4/18 | 22:30 | | 8 | 2013/4/18 | 23:30 | | 9 | 2013/4/19 | 00:30 | | 21 | 2013/4/19 | 00:30 | | 22 | 2013/4/19 | 01:00 | | 7 | 2013/4/19 | 01:30 | | 16 | 2013/4/19 | 02:00 | SELECT s.shop_name, g.staff_name, g.staff_id, w.staff_start, w.staff_end FROM work_master w LEFT JOIN staff_master g ON w.staff_master_id = g.staff_id LEFT JOIN shop_master s ON w.shop_id = s.id WHERE w.work_date BETWEEN '2013/4/18' AND '2013/4/19' AND w.staff_start >= '22:00' GROUP BY w.staff_master_id ORDER BY w.work_date ASC, w.staff_start ASC Above query gave me this output : |_Id_|___date___|__hour__| | 2 | 2013/4/18 | 22:00 | | 5 | 2013/4/18 | 22:30 | | 8 | 2013/4/18 | 23:30 | | 19 | 2013/4/19 | 22:00 | | 29 | 2013/4/19 | 22:30 | | 41 | 2013/4/19 | 23:00 | | 7 | 2013/4/19 | 01:30 | | 16 | 2013/4/19 | 02:00 | Appreciate for any help Link to comment https://forums.phpfreaks.com/topic/277098-select-with-a-condition-how-to/ Share on other sites More sharing options...
Barand Posted April 18, 2013 Share Posted April 18, 2013 I'm curious how the 01:30 and 02:00 got in there when the condition states >= '22:00'. From your explanation it looks as though you want (today AND after 17:00) OR (tomorrow) You need to change your date and times from varchar to DATE and TIME, or DATETIME. Your current format won't work when you get to 2013/9/30 and 2013/10/1. The formats should be yyyy-mm-dd and hh:ii:ss Link to comment https://forums.phpfreaks.com/topic/277098-select-with-a-condition-how-to/#findComment-1425579 Share on other sites More sharing options...
sniperscope Posted April 19, 2013 Author Share Posted April 19, 2013 I'm curious how the 01:30 and 02:00 got in there when the condition states >= '22:00'. From your explanation it looks as though you want (today AND after 17:00) OR (tomorrow) You need to change your date and times from varchar to DATE and TIME, or DATETIME. Your current format won't work when you get to 2013/9/30 and 2013/10/1. The formats should be yyyy-mm-dd and hh:ii:ss Different approach. Unfortunately, i cannot change db construction. What i want is list today's worker after 10pm and include everybody who works tomorrow. So, in my query bring today's and tomorrow's workers who works after 10pm. Link to comment https://forums.phpfreaks.com/topic/277098-select-with-a-condition-how-to/#findComment-1425746 Share on other sites More sharing options...
Barand Posted April 19, 2013 Share Posted April 19, 2013 I need that display staff_id who works today and after 5pm and all from tomorrow. What i want is list today's worker after 10pm and include everybody who works tomorrow. Are you sure you know what you want? Link to comment https://forums.phpfreaks.com/topic/277098-select-with-a-condition-how-to/#findComment-1425879 Share on other sites More sharing options...
streamland Posted April 23, 2013 Share Posted April 23, 2013 What i want is list today's worker after 10pm and include everybody who works tomorrow. I think sniperscopefeels difficult with his english and like to say What i want is list today's worker after 10pm and include everybody who will work tomorrow. and I need that display staff_id who works today and after 5pm and all from tomorrow. I need to display staff_id who will work today and after 5pm and the same way tomorow. Link to comment https://forums.phpfreaks.com/topic/277098-select-with-a-condition-how-to/#findComment-1426159 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.