MySQL find first available weekend

Question

I have a table which holds restaurant reservations. It just has an restaurant_id and a date column which specify the actual date (we are talking about whole day reservations).

I want to find out when is the next available weekend for a particular restaurant. A "weekend" is either Saturday or Sunday. If one of them is available, then we have an available weekend.

The query should, of course, consider the current time to calculate the next weekend.

Can anyone help?

Here's the table structure and data for the "dates" table which holds all reservations made so far:

id    id_venue    date  
12    1           2011-04-22  
13    1           2011-04-23  
14    1           2011-04-24  
15    1           2011-04-30  
16    1           2011-05-07  
17    1           2011-05-08

As you can see, the weekend of 23-24 is full, so the one of 7-8 May. What I need to find is the date of 2001-05-01 which is the first available Saturday OR Sunday after today's date.

Answer 1

I think the others are missing the question... They think your table may already be POPULATED with all weekends and some status as to open or not... My guess is that your table only HAS a record IF it is reserved... thus you need to find records that DO NOT EXIST AT ALL... based on some automated Look for dates...

This is a modification to another post I've done here

Although I didn't change the context of the query, I only put in the columns associated to YOUR table. I understand you are only going against a single venue table and so am I (actually). However, to understand the "JustDates" alias, this INNER PRE-QUERY is creating a dynamically populated table of ALL DATES by doing a Cartesian join against ANY other table.. in this case, your "Venue" table of reservations (I didn't see your actual table name reference explicitly, so you'll have to change that). So, this in essence creates a table of all dates starting from whatever "today" is and goes forward for 30 days (via limit), but could be 40, 50, 300 or as many as you need.. provided the "YourVenueTable" has at least as many records as days you want to test for. (same clarification in post this was derived from). This result set going out 30, 40 or however many days is pre-filtered for ONLY the given day of week of 1-Sunday or 7-Saturday... So it should return a result set of only Apr 23, Apr 24, Apr 30, May 1, May 7, May 8, May 14, May 15, May 21, May 28, etc.

So NOW you have a dynamically created result set of all possible days you are considering moving forward. Now, that gets joined to your actual Venue Reservations table and is filtered to ONLY return those DATES where it is NOT found for the id_venue you are concerned about. In your data example it WOULD find a match on Apr 23 and 24 and NOT return those records. Same with Apr 30... However, it WILL find that the record in the prequalifying list that includes May 1 will NOT find the date match in the venue table and thus include that as you are anticipating... It will then continue to skip May 7 and 8, then return May 14, 15, 21, 28, etc...

select JustDates.OpenDate
  from 
      ( select
         @r:= date_add( @r, interval 1 day ) OpenDate
      from
         ( select @r := current_date() ) vars,
         Venue 
      LIMIT 30 ) JustDates
   where
      DAYOFWEEK( JustDates.OpenDate ) IN ( 1, 7 )
      AND JustDates.OpenDate NOT IN
          ( select Venue.date
                from Venue
                where Venue.id_venue = IDYouAreInterestedIn
                  and Venue.Date = JustDates.OpenDate )
    order by 
       JustDates.OpenDate

Note, and per the other reservations posting, the query for reservation date availability dates doing a limit of 30 above can be ANY table in the system as long as it has AT LEAST as many days out as you want to look forward for reservations... If you want all availability for an upcoming year, you would want 365 records in the table used for a Cartesian result to get the @r cycling through dynamically created "date" records.

Answer 2

SELECT ...... DAYOFWEEK(`date`) as `num` FROM .... WHERE  num = 1 OR num = 7

I don't know how u wanna check "availability"

Answer 3

How about?:

SELECT * FROM table WHERE (DAYOFWEEK(date)=1 OR DAYOFWEEK(date)=7) AND restaurant_id =$RESTAURANTID AND date >  CURDATE() ORDER BY date ASC LIMIT 1

Answer 4

Set the number of days from today until the next Saterday (if 0 then today is Saterday) Assuming that if today is Sunday you only want reservations for the next full weekend.

select @OffsetSaterday:= mod((8-DayOfWeek(CurDate())+7,7);

You have not supplied enough info to know how the reservation database looks, so I'm going to guess here.

Every restaurant has seats:

Table seats
  id: integer primary key
  rest_id: integer  #link to restaurant
  desc: varchar(20) # description of the seat.

Table restaurant
  id: integer primary key
  other fields.....

Table Reservation
  id: integer primary key
  reservation_date: date
  seat_id: integer

The select statement to get all available seats for next weekend is:

select @OffsetSaterday:= mod((8-DayOfWeek(CurDate())+7,7);
select s.*, rst.* from seats s
  inner join restaurant rst on (rst.id = seats.rest_id)
  left join r on (r.seat_id = s.id 
    and r.reservation_date between 
      date_add(curdate(),interval @OffsetSaterday day) and
      date_add(curdate(),interval @OffsetSaterday+1 day)
where r.id is null
order by s.rest_id, s.desc;

You might be able to combine the two selects into one, but MySQL does not guarantee the order in which expressions get evaluated, so I would recommend against that.