Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Unanswered: finding next available appointment

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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