0

I have a problem with a scheduling system that has one table with 2 date columns.
Very similar to the problem outlined in this question: mysql-find-free-time-slots-between-appointments

CREATE TABLE bookings
(`id` int, `StartDate` datetime, `EndDate` datetime, `set_id` int);


INSERT INTO bookings
    (`id`, `StartDate`, `EndDate`, `set_id`)
VALUES
    (1, '2013-11-20 08:59:00', '2013-11-20 09:00:00', 6),
    (2, '2013-11-20 09:00:00', '2013-11-20 09:30:00', 6),
    (3, '2013-11-20 09:40:00', '2013-11-20 10:00:00', 6),
    (4, '2013-11-20 10:20:00', '2013-11-20 10:30:00', 6),
    (5, '2013-11-20 16:20:00', '2013-11-20 16:50:00', 6),
    (6, '2013-11-20 22:00:00', '2013-11-20 22:01:00', 6)

Based on an interval of X minutes (say 10 for the example), I want to find the next 5 start times available for an appointment within the date ranges in the table that can be used to add new appointments. It must also be able to look ahead up to 3 months for available slots.

But in my scenario I would need it to return.... 

| AVAILABLE_FROM      |
-----------------------
| 2013-11-20 09:30:00 |  
| 2013-11-20 10:00:00 |  
| 2013-11-20 10:10:00 |  
| 2013-11-20 10:30:00 |  
| 2013-11-20 10:40:00 |

If anyone can help with this problem I would be very grateful.

Regards
SlapMe

Community
  • 1
  • 1
SlapMe
  • 9
  • 1
  • Presumably, only certain times of day and certain days of week are bookable. How do you define that? – Strawberry Dec 02 '13 at 19:03
  • Hi Strawberry, That is something I forgot to mention. The available scheduling times in a day are between 09:00 and 22:00. – SlapMe Dec 02 '13 at 19:19
  • Have you written anything? – AgRizzo Dec 03 '13 at 12:03
  • Hi AgRizzo, to be honest I am stuck. I have tried to and not able to find how to get the results I require. I am guessing I need to create a function to handle this but am at a loss as to how to do this. – SlapMe Dec 04 '13 at 20:12

0 Answers0