SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 SQL:
    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 SQL:
    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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by robbot View Post
    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?

    Quote Originally Posted by robbot View Post
    Ideally, I'd like to set conditions specifying the earliest start time and required appointment duration
    what sorts of conditions?

    Quote Originally Posted by robbot View Post
    ... they go on about pivot table and analytic functions and all sorts!
    i hears ya, pal, i hears ya!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi

    Quote Originally Posted by r937 View Post
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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