finding next available appointment

robbot · #1 (**permalink**) 12-26-08, 14:47

hi all

I'm a relative newbie to mysql and sql in general. I'm using Mysql 5 to build an appointment booking system. Here's the schema for the table in question:

Code:

CREATE TABLE `bookings` (   
`id` int(11) NOT NULL auto_increment,   
`emp_id` int(11) default NULL,   
`starts_at` datetime default NULL,   
`ends_at` datetime default NULL,   
 PRIMARY KEY  (`id`) 
)

I want to run a select query that looks for gaps between the value for ends_at on one row and the value for starts_at on the next row. After days of trawling forums and web sites here's what I've come up with:

Code:

SELECT *, TIMEDIFF(b.starts_at, a.ends_at) AS 'length'
FROM bookings AS a 
JOIN bookings AS b ON a.emp_id = b.emp_id 
  AND a.ends_at < b.ends_at
GROUP BY a.ends_at

don't ask why I'm using that query - I'm in way over my head and that's the sad result of a lot of trial and error. Ideally, I'd like to set conditions specifying the earliest start time and required appointment duration, but right now I'd settle just for a full list of available appointments.

I'm not even sure if this is possible in mysql. I've seen solutions for other DBs but they go on about pivot table and analytic functions and all sorts!

any help gratefully received
rob

mike_bike_kite · #2 (**permalink**) 12-27-08, 06:24

The chances of you finding a perfect solution to any problem on the internet is quite low - you'll generally just find good pointers. I'd just try to improve the SQL you have (or start afresh) and go through the following iterations:

Select out all the gaps for an employee
then show the length of each gap
then order by start time
then limit results to just one row
might also want to limit when the gaps can be ie 9 to 5 and not lunchtimes or weekends
also think about how to show 1st appointment next week

You might want to ask your teacher why he has a primary key on the id field - what benefit is it actually offering?

r937 · #3 (**permalink**) 12-27-08, 10:33

Code:

SELECT start_of_gap AS next_gap
  FROM (
       SELECT b1.ends_at    AS start_of_gap
            , b2.starts_at  AS end_of_gap 
            , ( UNIX_TIMESTAMP(b2.starts_at) -
                UNIX_TIMESTAMP(b1.ends_at) )
              / 3600.00 AS gap_length_hours
         FROM bookings AS b1
       LEFT OUTER
         JOIN bookings AS b2
           ON b2.emp_id = b2.emp_id  
          AND b2.starts_at =
              ( SELECT MIN(starts_at)
                  FROM bookings
                 WHERE emp_id = b1.emp_id
                   AND starts_at > b1.ends_at )
        WHERE b1.emp_id = 21
       ) AS gap_data
 WHERE start_of_gap > '2008-12-26 09:00:00' 
   AND gap_length_hours > 0.5 
ORDER
    BY next_gap LIMIT 1

this still needs tweaking for the situation that arises when the last gap is open-ended, i.e. when the LEFT OUTER JOIN returns no next booking

Internet Services

Web Development

Digital Marketing

Consumer Tech