CODEWITHSUNDEEP
mysqlsql
Rob4236
Rob4236

Reputation: 23

SQL Query for Checking Calendar availability?

I have a sql database which contains EVENTS (See Below)

Events Table

I am trying to write a query that will give me back a List of Slots that are available ONLY !

For example if we used the staff_id which is "3" (Sarah), using a 15 minute interval starting from 9am - 5pm does Sarah have free for this day?

so lets say the user would choose Monday 18th March and Sarah, I would like to query to go and get all of the events for that day in Sarah's diary and then compare then against each slot and remove any slots that Sarah is already busy (i.e. Has an Event in already)

The problem is that Sarah has an appointment between 1pm - 2pm I would like to see 2pm slot available as she is free after this event.

This is a PHP App, so I may have to do the Compare outside of SQL after I have the Events for Sarah, but would like to do as much as possible with SQL before getting an array of available slots?

Some Sample Events

╔══════════╤══════════╤═════════════════════╤═════════════════════╗
║ event-id │ staff_id │ event_start         │ event_end           ║
╠══════════╪══════════╪═════════════════════╪═════════════════════╣
║ 1        │ 1        │ 2020-04-21 13:00:00 │ 2020-04-21 16:00:00 ║
╟──────────┼──────────┼─────────────────────┼─────────────────────╢
║ 2        │ 1        │ 2020-04-21 09:00:00 │ 2020-04-21 10:30:00 ║
╟──────────┼──────────┼─────────────────────┼─────────────────────╢
║ 3        │ 1        │ 2020-04-21 17:00:00 │ 2020-04-21 17:30:00 ║
╟──────────┼──────────┼─────────────────────┼─────────────────────╢
║ 4        │ 2        │ 2020-04-21 12:00:00 │ 2020-04-21 13:00:00 ║
╚══════════╧══════════╧═════════════════════╧═════════════════════╝

So for the 21st of April, I would like not get any 15 Min Slots between 1300-1600 OR 0900 - 1030 OR 1700 - 1730 for Staff member 1

Upvotes: 0

Views: 1971

Answers (1)

IVO GELOV
IVO GELOV

Reputation: 14259

Let the table SLOTS contains the possible slots

+----+------------+----------+
| ID | SLOT_START | SLOT_END |
+----+------------+----------+
| 1  |   10:00:00 | 11:00:00 |
+----+------------+----------+

Then for Sarah (staff_id == 1) we can find the available slots for a given date (e.g. 2020-04-21) like this:

SELECT slot_start, slot_end FROM slots
WHERE NOT EXISTS(
  SELECT 1 FROM events
  WHERE 
    staff_id = 1
    AND
    (event_start BETWEEN '2020-04-21 00:00:00' AND '2020-04-21 23:59:59'
    OR
    event_end BETWEEN '2020-04-21 00:00:00' AND '2020-04-21 23:59:59')
    AND
    (
      CONCAT('2020-04-21 ',slot_start) BETWEEN event_start AND event_end
      OR CONCAT('2020-04-21 ',slot_end) BETWEEN event_start AND event_end
    )
)

Upvotes: 1

Related Questions