Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    7

    Unanswered: Return Dates in a Range

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ...and from your numbers table you can create a calendar table too
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •