Logic for retrieving earliest available start date

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.

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.

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

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.