Query for available time

[Gazzza]

Hi,

I'm a new to sql so forgive me if this is really basic

I've made a database for booking workspaces for a date, start time and end time. I have written a query that the user selects a workspace, date, start time and end time. Having converted the date and time into timestamps that look like yyyymmddhhmmss I can do a select on the booking for spaces that have bookings between the start and end time stamps. If eof then the space is not booked or there is no conflicting bookings so allow the booking request.

Now I have been asked to show all of the available spaces from a user's selected date starttime and endtime but I can't think how using Sql. I have an earlest available of 7am and latest available time of 7pm that a booking can be booked and the booking can only last for a day max. Can anyone tell me the best way of getting the available bookings.

Thanks,

Garry

 

[yaffle]

I sounds like you're going to try selecting things that aren't there - always tricky ;-)
I'd suggest using a second temporary table, populating it with the possible slots, in one hour increments or whatever granularity is required, then select slots from this secondary table where timestamps don't coincide with the existing bookings.

 

[fluteplr]

Sorry I have a really heavy week and can't work on this for you. However I highly suggest picking up the following book.

SQL for Smarties: Advanced SQL Programming
by Joe Celko

He has a whole section on problems like this. IMHO the guy is a genius.