March 24, 2023 at 8:57 am
Hello everyone. I hope everyone's ok.
I'm doing a query for a restaurant and I'm trying to group the info by meal name like lunch, dinner, etc.
After I group it, when I'm searching for every records for the specific meal 'Lunch', it seams it's showing records until 16h. (4PM) after I've said between 10 and 15. Let me show you my SQL:
Hello everyone. I hope everyone's ok.
I'm doing a query for a restaurant and I'm trying to group the info by meal name like lunch, dinner, etc.
After I group it, when I'm searching for every records for the specific meal 'Lunch', it seams it's showing records until 16h. (4PM) after I've said between 10 and 15. Let me show you my SQL:
SELECT
DH.TABLE_ID AS 'Table',
CAST( CONCAT(DH.OPENING_HOUR_H,':',DH.OPENING_HOUR_M) as time) AS 'Time',
CAST(DATEADD(Minute, DATEDIFF(Minute, CAST( CONCAT(DH.OPENING_HOUR_H,':',DH.OPENING_HOUR_M) as time), CAST( CONCAT(DH.CLOSING_HOUR_H,':',DH.CLOSING_HOUR_M) as time)),0) AS time(0)) AS Duration,
DH.PERSONS AS 'People#',
(CASE
WHEN CAST(DH.OPENING_HOUR_H AS INT) BETWEEN 10 AND 15 THEN 'Lunch'
WHEN CAST(DH.OPENING_HOUR_H AS INT) BETWEEN 15 AND 18 THEN 'Snack'
ELSE 'Dinner'
END) AS 'Meal Type'
Any ideas on how to solve this?
Thank you all in advance
March 24, 2023 at 9:04 am
Between is inclusive, so Lunch will be any meal from 10:00 to 15:59 and Snack will be any meal from 15:00 to 18:59 according to your logic above, so you have a 1 hour overlap between Lunch and Snack.
If Lunch is 10:00 to 14:59, then you will need to use BETWEEN 10 AND 14, or use DH.OPENING_HOUR_H >= 10 AND DH.OPENING_HOUR_H < 15
March 24, 2023 at 9:20 am
Thank you so much @Ant-Green
You're absolutely right. I think I need a coffee....(still sleeping here)
March 27, 2023 at 7:32 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply