MySQL - Find date ranges matching a list of months

I have several rows in a table, each containing a start date and an end date. The user has a checkbox for each month of the year. I 开发者_JAVA技巧need to determine which rows contain a date range that includes any of the user's chosen months.

It's easy to check the start & end months by, for example, MONTH(start_date) IN ($month_list), but this approach won't match any months between the two dates.

So I suppose what I'm asking is: is there a way of obtaining the inclusive months from a date range purely in SQL?

I assume you would want to include data rows where the date range spans or intersects with the selected periods - in which case, I'd shove the user selected periods into a table and do a fuzzy join, something like.....

SELECT DISTINCT at.*
FROM a_table at, user_periods up
WHERE at.start_date<=up.end_date
AND at.end_date>=up.start_date
AND up.trans_id=$SOME_VAR

(the trans_id just allows the table to be used for multiple operations)

To minimise the effort here, the user_periods table should have an index on start_date and end_date, and similar for a_table.

Can something like this help?

WHERE 
  MONTH(start_date) < MONTH_YOU_ARE_CHECKING and 
  MONTH() > MONTH_YOU_ARE_CHECKING

If you need to check all at once you can do a list of all the months and after delete from the list the month that the user choose, and after compare against the list. It will be better with a pseudocode example :)

MONTHS = 1,2,3,4,5,6,7,8,9,10,11,12
USER_SELECTED_MONTHS= 1,6,8,9,12
LIST_TO CHECK = 2,3,4,5,7,10,11

so, now you can do:

MONTH(start_date) NOT IN (2,3,4,5,7,10,11)

What do you think, could it help you?

regards