Logic for retrieving earliest available start date

Databases
1

I am doing some work for a charity who have a series of rooms available for hire.

Each room can be hired in increments of 1 hour from 9am until 5pm. When they receive a booking, the start time and end time are stored in a simple bookings table.

In order to populate the drop downs that allow the user to select their booking time, I need to get the earliest possible start time. So if there are no bookings it starts at 9am. But if there is a 9am-11am booking then it starts at 11am. But if ther is also a 11am-1pm booking then it will need to start at 1pm. Equally, if there is also a booking from 4pm-5pm then it should stop at 4pm.

I just need the earliest possible start time and the latest possible finish time, which I can then use to add all of the valid values to my drop downs.

I hope I explained the problem clearly. Any help would be greatly appreciated.

2

And what if you’ve already booked the room from, let’s say, 10am-11am, 1pm-2pm and 3pm-4pm?
In that case the earliest possible start time would be 9am, and the latest possible finish time 5pm. But you wouldn’t be able to do much with those 2 limits.

3

That would be fine - I already have logic that checks the times they submit for clashes with other bookings.

4

Search the forum or web on ROOM BOOKING and see what you come up with, this has been asked and answered a number of times.