Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    43

    Unanswered: List of next 30 dates

    I'd like to select the next 30 dates into a column and I'll be joining a table to the dates, but the foundation will be the dates.

    Just a list like this:

    2007-11-29
    2007-11-30
    2007-12-01
    2007-12-02
    2007-12-03

    ... and so on. The idea is that I'll outer join a table containing bookings onto this, to check if anything is booked on any of these dates. I could do this programmatically in php, but if possible, I'd like MySQL to provide the dates even if there are no bookings.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so rather than the next 30 dates with bookings.. Im guessing it would make more business sense to look at bookings in the next n periods.if the customer wants a specific facility then that to should be added to the query

    I can see a potential customer asking what dates have you got free in (say) January 2008, or waht have you got free in the week commencing 31st Dec. or when is the "oddish" room available for this time slot.

    so Im guessing your query should return any bookings by venue/event by day, form that for a customer query you would then need to work out what dates are free, or if its an internal management / capacity planning query what dates are actually booked, in either case your front end should probably present that data as diary week/month page.

    if you are feeling especially creative and you have a production calendar you could do a left join and extract all dates in your calendar and any bookings which match those dates.

  3. #3
    Join Date
    Mar 2003
    Posts
    43
    It's a calendar for booking car installations (hands-free kits, alarms, alco-locks etc.) and there's a certain number of work hours available each day, so I'm looking to create a list of the next 10 dates where there are hours still available.

    I've already written a bunch of sql to fetch upcoming bookings and how many hours are still available on those dates, but on dates where there are no bookings, nothing shows up, obviously, so I'm looking for a query where the base is the next x number of days, and I'll outer join other tables to check if the number of available hours on each date is affected by existing bookings or "deviations" as I've chosen to call them (people on vacation etc.).

    I just need sql to select a column of the next x dates to get me started, but I don't even know if that's possible.

  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Re

    Hi,

    Can't u do a select with addday(30) from the sysdate
    and then group-order it by date?

    I havent tested it, but i think it would be possible.

    Greetings

  5. #5
    Join Date
    Mar 2003
    Posts
    43
    If you mean DATE_ADD(), sure I can get the date 30 days from now, but how do I get the dates in between now and then?
    Code:
    SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY);

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  7. #7
    Join Date
    Mar 2003
    Posts
    43
    Thanks a lot, I'll check it out!

Posting Permissions

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