SQL Query for available appointment slots over a month

Question

Currently I have a query:

select a.day_of_week,
       a.time,
       a.id,
       '2012-01-10' as scheduleDate,
       a.max_customers,
       (SELECT count(b.id) 
        from appointments b 
        where b.date = '2012-01-10' AND 
              b.schedule_id = a.id) as current_customers 
from schedules a 
where a.showroom_id = 1 and
      a.day_of_week = DATE_FORMAT('2012-01-10','%W')

Assume '2012-01-10' to be variable.

We have an application that is a calendar. When a user clicks a day this query is run to return a list of slots and the amount of people currently booked (current_customers).

Here's the quirk - Now they want to be able to retrieve an entire month at a time. Currently our developer essentially does 30 queries to do this via a FOR loop in PHP.

I was thinking of creating a stored procedure to do this but you're still using a loop. A union is another option but then thats a large large query I'd think.

Does anyone have a good idea of how to create a query that would run the above but for all days in a month or a variable amount of days?

Table Structure - appointments

Field Type Allow Null Default Value id int(11) No
customer_id int(11) No
associate_id int(11) Yes schedule_id int(11) No
date date No
outcome_id int(11) Yes notes text No
is_active tinyint(1) No 1 created_at timestamp No 0000-00-00 00:00:00 updated_at timestamp No 0000-00-00 00:00:00

schedules

Field Type Allow Null Default Value id int(11) No
showroom_id int(11) No
day_of_week enum('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') No
time time No
max_customers int(11) No
is_active tinyint(1) No 1 created_at timestamp No 0000-00-00 00:00:00 updated_at timestamp No 0000-00-00 00:00:00

could you provide table structure of both tables? those would be much better to understand... — Fahim Parkar, Jan 19 '12 at 15:12

score 0 · Answer 1 · edited May 23 '17 at 12:11

Per clarification on how my subquery with @sqlvars is working, take a look at a similar reservation lookup I offered here. The subquery just looks at any table in your system to grab as many records as days you want to go out... in this sample, 30 days. All you have to do is change the starting date of '2012-01-10', or use "current_date()" so it always goes from the current date +30 and no parameters required otherwise.

select
      a.day_of_week, 
      a.time, 
      a.id, 
      @r as scheduleDate, 
      a.max_customers, 
      (SELECT count(b.id) 
          from appointments b 
          where b.date = @r 
          AND b.schedule_id = a.id) as current_customers 
   from
      ( SELECT 
                 @r:= date_add(@r, interval 1 day ) OpenDate
            FROM 
                 (select @r := '2012-01-10') vars,
                schedules limit 30 ) JustDates,
      schedules a 
   where 
          a.showroom_id = 1 
      and a.day_of_week = DATE_FORMAT( @r,'%W')

SQL Query for available appointment slots over a month

1 Answers1