SQL query about appointments....

Question

Hello! Suppose we have this table..

CREATE TABLE `appointments` (
  `idappointments` int(11) NOT NULL AUTO_INCREMENT,
  `timeStart` time DEFAULT NULL,
  `timeEnd` time DEFAULT NULL,
  PRIMARY KEY (`idappointments`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8$$

assumption

Suppose that a range between timeStart and timeEnd cant exist again... I mean that if we intersect all the ranges in the table the result would be EMPTY ,0 , null.An appointment cant cooexist with another..

So what i want to do is a time suggestion if the desired time is occupied... A proposal before and a proposal after the desired time....

So i was wondering instead of writing code to do this if i could write an SQL query to find the nearest empty ranges......

Ex.. timeStart - NEAREST_TO_TIMESTART_TIMEEND >'10 minutes' whereas 10 minutes the duration

Answer 1

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

1. Create a table that only holds incrementing numbers - easy to do using an auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Populate the table using:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...for as many values as you need.

3. Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-01-01" and "2010-01-02" with your respective start and end dates (but use the same format, YYYY-MM-DD HH:MM:SS) -

   SELECT x.dt
     FROM (SELECT TIME(DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE)) AS dt
             FROM numbers n
            WHERE DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE) <= '2010-01-02' ) x
   

4. LEFT JOIN onto your table of data based on the datetime portion.

This will show you the first available slot:

       SELECT x.dt
      FROM (SELECT TIME(DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE)) AS dt
              FROM numbers n
             WHERE DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE) <= '2010-01-02' ) x
    LEFT JOIN APPOINTMENTS a ON x.dt BETWEEN a.timestart AND a.timeend
        WHERE a.idappoinment IS NULL
          AND x.dt > @your_minimum_datetime
     ORDER BY x.dt
        LIMIT 1

This will show you availability for the entire day:

       SELECT x.dt,
              CASE 
                WHEN a.idappoinment IS NULL THEN 'available'
                ELSE 'booked'
              END AS isbooked 
      FROM (SELECT TIME(DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE)) AS dt
              FROM numbers n
             WHERE DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE) <= '2010-01-02' ) x
    LEFT JOIN APPOINTMENTS a ON x.dt BETWEEN a.timestart AND a.timeend
     ORDER BY x.dt

Why Numbers, not Dates?

Simple - dates can be generated based on the number, like in the example I provided. It also means using a single table, vs say one per data type.