Bojan Kogoj Bojan Kogoj - 7 months ago 20
SQL Question

Get row between day groups

Days defined as:

0 - Monday
1 - Tuesday
2 - Wednesday
3 - Thursday
4 - Friday
5 - Saturday
6 - Sunday


I have several groups in database, which have start_day, end_day,...

Part of the SQL is this

SELECT *
FROM groups AS pg
WHERE ...
AND pg.start_day <= 6
AND pg.end_day >= 6


Now if my group has

start_day: 0
end_day: 4


or

start_day: 5
end_day: 6


the above SQL works completely fine. The problem becomes in this case

start_day: 4
end_day: 5


and

start_day: 6
end_day: 3


when I try to get second group, for example for day 6 since start_day is greater than end_day. What would be the best way to make this work?

Basically I have prices that depend on day of the week, and I need to get the correct price for given day of week.

Answer

If I understand the problem, it seems that day ranges that wrap around between Sunday and Monday are causing the problem?

The additional clause below should account for this case:

WHERE (
    ( start_day <= end_day 
        and ( start_day <= day_of_week and end_day >= day_of_week )
    ) 
    OR 
    ( start_day > end_day 
        and ( day_of_week >= start_day or day_of_week <= end_day )
    ) 
)
Comments