Query to find available timeslots

Question

I have a table called appointments and a table called shifts. In the table appointments, there is the shift_id, so it looks something like this:

id | start_date | end_date | shift_id ( FK )

An example shift is 09:00 to 17:00 and it contains a couple of appointments in it:

09:30 - 10:10 | 11:30 - 11:50 | 13:00 - 14:00

I am currently using this query to get the available time slots, but it has an edge case issue. It does not find the start from 9 - 9:30.

SELECT Available_from, Available_to
        FROM (
        SELECT @lasttime_to AS Available_from, start_date AS Available_to, @lasttime_to := end_date
        FROM (SELECT start_date, end_date
               FROM appointments
                WHERE shift_id = " . $shiftData->id . "
                AND end_date <= '" . $shiftData->end_date->toDateTimeString() . "'
                AND start_date >= '" . $shiftData->start_date->toDateTimeString() . "'
               UNION ALL
             SELECT '". $shiftData->start_date->toDateTimeString() ."', '". $shiftData->start_date->toDateTimeString() ."'
                   ORDER BY end_date 
        
             ) e
        JOIN (SELECT @lasttime_to := NULL) init) x
        WHERE Available_to > DATE_ADD(Available_from, INTERVAL " . $serviceDurations . " MINUTE)

What's missing in this? How can I get the start to show up in the query?

http://sqlfiddle.com/#!9/e5292d/2

Answer 1

First, to address your original SQL (in the fiddle).

For the first row, @lasttime_to is NULL and causes problems.

Need to use the "shift start time" conditionally for the null case (first row).

See LAG for another approach. That's really what the @variable hack is trying to replace, before LAG was available in MySQL.

Try this:

SELECT Available_from, Available_to
  FROM (
    SELECT COALESCE(@lasttime_to, '2022-03-15 10:00:00') AS Available_from, start_date AS Available_to, @lasttime_to := end_date
    FROM (SELECT start_date, end_date
             FROM appointments
             WHERE shift_id = 4600
             AND end_date <= '2022-03-15 17:00:00'
             AND start_date >= '2022-03-15 10:00:00'
             UNION ALL
               SELECT '2022-03-15 17:00:00', '2022-03-15 17:00:00'
               ORDER BY start_date 
           ) e
    JOIN (SELECT @lasttime_to := NULL) init) x
    WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;

The result:

Available_from	Available_to
2022-03-15 10:00:00	2022-03-15 10:30:00
2022-03-15 12:15:00	2022-03-15 15:00:00
2022-03-15 15:40:00	2022-03-15 17:00:00

Here's something with window functions too. I've rearranged the logic to avoid multiple magic constants related to your shift start/end detail. I didn't remove all the magic, however.

I still prefer the more dynamic approach that I removed.

WITH shift (shift_start, shift_end) AS (
       SELECT '2022-03-15 10:00:00', '2022-03-15 17:00:00'
     )
   , e0 AS (
       SELECT shift_id, start_date, end_date
         FROM appointments
        WHERE shift_id = 4600
          AND end_date   <= (SELECT shift_end   FROM shift)
          AND start_date >= (SELECT shift_start FROM shift)
        UNION ALL
       SELECT 4600, shift_end, shift_end FROM shift
        ORDER BY start_date 
     )
   , e AS (
      SELECT e0.*
           , LAG(end_date) OVER (PARTITION BY shift_id ORDER BY start_date) AS lasttime_to
        FROM e0
     )
SELECT shift_id, Available_from, Available_to
  FROM (
       SELECT shift_id
            , CAST(COALESCE(lasttime_to, shift_start) AS datetime) AS Available_from
            , start_date AS Available_to
         FROM e
         CROSS JOIN shift
      ) x
 WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;

Slightly different version:

WITH e0 AS (
       SELECT a.shift_id, a.start_date, a.end_date
            , s.start_date AS shift_start
            , s.end_date   AS shift_end
         FROM appointments AS a
         JOIN shifts       AS s
           ON a.shift_id = 4600
          AND a.shift_id = s.id
          AND a.end_date   <= s.end_date
          AND a.start_date >= s.start_date
        UNION
       SELECT id, end_date, end_date
            , start_date  , end_date
         FROM shifts WHERE id = 4600
     )
   , e AS (
      SELECT e0.*
           , LAG(end_date) OVER (PARTITION BY shift_id ORDER BY start_date) AS lasttime_to
        FROM e0
     )
SELECT shift_id, Available_from, Available_to
  FROM (
       SELECT shift_id
            , CAST(COALESCE(lasttime_to, shift_start) AS datetime) AS Available_from
            , start_date AS Available_to
         FROM e
      ) x
 WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;

The fiddle