finding next available appointment

[robbot]

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:

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:

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

[r937]

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.

please explain the role of the emp_id column

also, is the query supposed to return results for a specific employee, all employees, or what?

Ideally, I'd like to set conditions specifying the earliest start time and required appointment duration

what sorts of conditions?

... they go on about pivot table and analytic functions and all sorts!

i hears ya, pal, i hears ya!!

[robbot]

hi

please explain the role of the emp_id column

also, is the query supposed to return results for a specific employee, all employees, or what?

each appointment belongs to a specific employee and the emp_id column is the foreign key linking to the employees table. The query I want would only return results for a specific employee.

what sorts of conditions?

I'd like to be able to restrict results by saying something like "WHERE some_value > '2008-12-26 09:00:00' " but that condition would apply to the returned values, not the rows themselves. e.g. if the query returned the following calculated columns:

'available_from', 'available_to'

then I'd like 'available_from' to be >= the specified datetime

i hears ya, pal, i hears ya!!

I'm guessing (hoping!) that means there's a simpler way of doing this?

[r937]

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