select with a condition how to?

[sniperscope]

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

Edited April 18, 2013 by sniperscope

[Barand]

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

Edited April 18, 2013 by Barand

[sniperscope]

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.

[Barand]

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?

[streamland]

 

What i want is list today's worker after 10pm and include everybody who works tomorrow.

 

I think 

sniperscope

feels 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.