Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: Finding free appointment slots, double bookings allowed

    Hi all,

    given the following booking structure and data:
    Code:
    drop table if exists bookings;
    
    create table bookings (resource int, start_time datetime, end_time datetime);
    
    insert into bookings values
    (1, "2013-12-31 09:00:00", "2014-01-01 08:00:00"),
    (1, "2014-01-01 09:00:00", "2014-01-01 09:15:00"),
    (1, "2014-01-01 09:15:00", "2014-01-01 09:30:00"),
    (1, "2014-01-01 10:00:00", "2014-01-01 10:15:00"),
    (2, "2014-01-01 09:00:00", "2014-01-01 09:30:00"),
    (2, "2014-01-01 09:45:00", "2014-01-01 10:30:00");
    The following SQL will find free appointment slots*:
    Code:
    select a.resource, a.end_time as 'free_from', min(b.start_time) as 'free_to'
    from bookings a 
    join bookings b ON a.end_time <= b.start_time and a.resource = b.resource
    group by a.end_time, a.resource
    having a.end_time < min(b.start_time);
    i.e:
    Code:
    1	2014-01-01 08:00:00	2014-01-01 09:00:00
    1	2014-01-01 09:30:00	2014-01-01 10:00:00
    2	2014-01-01 09:30:00	2014-01-01 09:45:00
    Is there a way to find free appointment slots if resources have been double booked? E.g., given the additional data:
    Code:
    insert into bookings values
    (1, "2014-01-01 09:00:00", "2014-01-01 10:00:00"),
    (2, "2014-01-01 09:00:00", "2014-01-01 10:00:00");
    The result should be:
    Code:
    1	2014-01-01 08:00:00	2014-01-01 09:00:00
    Caveats:
    1. Must execute in a single statement - no additional tables or views can be created
    2. Can't use cursors
    3. Must work on MySQL 5.1

    * Adapted from http://www.artfulsoftware.com/infotr...tip.php?id=577
    Last edited by b.wildered; 04-18-14 at 20:11.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    3. Must work on MySQL 5.1
    If that was a key requirement,
    you might want to ask in MySQL - dBforums


    By the way,
    this might be an answer (I tesed it on DB2, see the following examples)
    Code:
    SELECT a.resource
         , a.end_time        AS free_from
         , MIN(b.start_time) AS free_to
     FROM  bookings a 
     INNER JOIN
           bookings b
      ON   b.resource = a.resource
      AND  b.end_time > a.end_time
     GROUP BY
           a.resource
         , a.end_time
     HAVING
           a.end_time < MIN(b.start_time)
    ;

    Some test results on DB2 ...

    Example 1: No overlapping.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     bookings
    ( resource , start_time , end_time ) AS (
    VALUES
      ( 1 , TIMESTAMP('2013-12-31 09:00:00') , TIMESTAMP('2014-01-01 08:00:00') )
    , ( 1 , '2014-01-01 09:00:00' , '2014-01-01 09:15:00' )
    , ( 1 , '2014-01-01 09:15:00' , '2014-01-01 09:30:00' )
    -- , ( 1 , '2014-01-01 09:00:00' , '2014-01-01 10:00:00' )
    , ( 1 , '2014-01-01 10:00:00' , '2014-01-01 10:15:00' )
    
    , ( 2 , '2014-01-01 09:00:00' , '2014-01-01 09:30:00' )
    -- , ( 2 , '2014-01-01 09:00:00' , '2014-01-01 10:00:00' )
    , ( 2 , '2014-01-01 09:45:00' , '2014-01-01 10:30:00' )
    )
    SELECT a.resource
         , a.end_time        AS free_from
         , MIN(b.start_time) AS free_to
     FROM  bookings a 
     INNER JOIN
           bookings b
      ON   b.resource = a.resource
      AND  b.end_time > a.end_time
     GROUP BY
           a.resource
         , a.end_time
     HAVING
           a.end_time < MIN(b.start_time)
    ;
    ------------------------------------------------------------------------------
    
    RESOURCE    FREE_FROM                  FREE_TO                   
    ----------- -------------------------- --------------------------
              1 2014-01-01-08.00.00.000000 2014-01-01-09.00.00.000000
              1 2014-01-01-09.30.00.000000 2014-01-01-10.00.00.000000
              2 2014-01-01-09.30.00.000000 2014-01-01-09.45.00.000000
    
      3 record(s) selected.
    Example 2: have been double bookeed.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     bookings
    ( resource , start_time , end_time ) AS (
    VALUES
      ( 1 , TIMESTAMP('2013-12-31 09:00:00') , TIMESTAMP('2014-01-01 08:00:00') )
    , ( 1 , '2014-01-01 09:00:00' , '2014-01-01 09:15:00' )
    , ( 1 , '2014-01-01 09:15:00' , '2014-01-01 09:30:00' )
    , ( 1 , '2014-01-01 09:00:00' , '2014-01-01 10:00:00' )
    , ( 1 , '2014-01-01 10:00:00' , '2014-01-01 10:15:00' )
    
    , ( 2 , '2014-01-01 09:00:00' , '2014-01-01 09:30:00' )
    , ( 2 , '2014-01-01 09:00:00' , '2014-01-01 10:00:00' )
    , ( 2 , '2014-01-01 09:45:00' , '2014-01-01 10:30:00' )
    )
    SELECT a.resource
         , a.end_time        AS free_from
         , MIN(b.start_time) AS free_to
     FROM  bookings a 
     INNER JOIN
           bookings b
      ON   b.resource = a.resource
      AND  b.end_time > a.end_time
     GROUP BY
           a.resource
         , a.end_time
     HAVING
           a.end_time < MIN(b.start_time)
    ;
    ------------------------------------------------------------------------------
    
    RESOURCE    FREE_FROM                  FREE_TO                   
    ----------- -------------------------- --------------------------
              1 2014-01-01-08.00.00.000000 2014-01-01-09.00.00.000000
    
      1 record(s) selected.

  3. #3
    Join Date
    Jul 2010
    Posts
    4
    Thank you very much. That is an elegant solution to a problem I was expecting would be far more convoluted.

Posting Permissions

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