I'm want to create database with stores. Each store has their own working hours in a week. Then I want to query the store and sort them by nearest store which is currently open. So far I create this schema but unfortunatly I cannot query it like I would like to. The schema looks like this
This is the query I'm using currently (for day: Saturday and hour: 3:15 PM):
SELECT so.day, so.store_id, so.open, so.close
FROM shop_openings so
ORDER BY FIELD(so.day, 6, 7, 1, 2, 3, 4, 5) ASC,
'15:15' COLLATE utf8mb4_unicode_520_ci
NOT BETWEEN CONCAT( LPAD( so.open, 2, '0'), ':00')
AND CONCAT( LPAD( so.close, 2, '0'), ':00'),
LPAD( so.close, 2, '0' ),
LPAD( so.open, 2, '0' )
But it query just like the screen above shows.
Now here's what the list I want to achieve:
Let we say that today is Saturday. We have 4 stores which only 2 of them works in Saturday. I want to recieve 4 results order by day and open/close hours. Below I selected which rows I want to get:
No other results. Only 4 results as we have 4 stores in store_table
. Nearest 2 shops opened in Saturday and then 2 shops opened in Monday.
I have no idea how the schema should like other than mine, so any suggestion are welcome.
Here is a SQL Fiddle: http://sqlfiddle.com/#!9/9709e/1/0
Best Answer
If you only want the next day, and sundays are closed you can use this query,, when you want the next two days You have to UNION another Query and exclude all previous shops like i did in the WHERE clause
You can replace the @searchday session variable with DAYOFWEEK(NOW()) to get the actual day
db<>fiddle here