sql between for DAYSOFWEEK

Question

I have a generalized search where I'm looking up average prices for the various days of the week. The problem is I'm not sure how to ask for an odd range. 1-7 works fine, (monday-sunday), but if the user passes a sunday to a tuesday... ie 7-2 it doesn't work.

1-7 (1,2,3,4,5,6,7) 7-2 (7,1,2)

etc.

How else can I pass a range that is more intelligent or something besides BETWEEN?

SELECT item_id, DAYOFWEEK(bookdate) as date, bookdate, AVG(price) AS price 
FROM `availables` WHERE (item_id = 16 and DAYOFWEEK(bookdate) BETWEEN 2 AND 7) 
GROUP BY DAYOFWEEK(bookdate)

Answer 1

Perhaps what you are looking for is the IN syntax:

SELECT item_id, DAYOFWEEK(bookdate) as date, bookdate, AVG(price) AS price 
FROM `availables` WHERE (item_id = 16 and DAYOFWEEK(bookdate) IN (7,1,2)) 
GROUP BY DAYOFWEEK(bookdate)

Answer 2

Do it with an 'in' clause, like this: dayofweek(bookdate) in (1,2,7)

This also allows the flexibility of non-continuous dates (select records for Monday and Friday only, for example)