Return Dates in a Range

wowdezign · #1 (**permalink**) 01-05-10, 12:54

I am trying to return a list of the dates that are found in date ranges in a result set.

For example, I have a reservations table that has a res_start column and a res_end column. I want to run a query that returns all the dates that fall into any of the ranges returned. Is this possible?

Important: I don't want to return a range, I want all the individual dates that fall in any of the ranges. I am using the dates in an availability table.

Thanks for any direction you can give.

r937 · #2 (**permalink**) 01-05-10, 13:08

see this thread and create a numbers table with as many numbers as the longest date range

then change the query as follows --

Code:

SELECT r.res_start + INTERVAL n DAY AS my_date
  FROM reservations AS r
CROSS
  JOIN numbers
 WHERE r.somekey = somevalue -- THIS LINE IS IMPORTANT
   AND r.res_start + INTERVAL n DAY <= r.res_end

the line marked important is where you select which reservation you want to generate the dates for

gvee · #3 (**permalink**) 01-06-10, 04:01

...and from your numbers table you can create a calendar table too

Internet Services

Web Development

Digital Marketing

Consumer Tech