Beginner: Checking availability on a booking table in SQL

Question

I'm writing a job vacancy database for a bit of fun (and to try and learn T-SQL/SQL Server and this is what I have in my applications table so far.

application_id  name         interviewer      location_id      from                  to
-----------------------------------------------------------------------------------------------------------
1               Joe Bloggs   Sarah Saunders   100              2008-12-25 00:00:00   2008-12-26 00:00:00
2               Barry White  Issac Hayes      100              2008-12-29 00:00:00   2008-12-30 00:00:00

It's easy enough to find out what bookings have been made for these dates; a simple select statement would find these out easily enough.

The only problem I have now is how to figure out what days DON'T contain bookings. I'd like to do a search on the following table to see what dates are available in the room with location_id 100 between "2008-12-25 00:00:00" and "2008-12-30 00:00:00" and have it return that there is no interview being held in the room from the 27th to the 28th.

I'm sure this is painfully easy, but please lay some SQL wisdom on me.

Similar to this: http://stackoverflow.com/questions/704176/how-to-implement-check-availability-in-hotel-reservation-system/704185

Answer 1

You can generate a temporary table containing your days (either in the upper layer or with a stored function, which would be better if that is for SQL-learning purpose), then OUTER JOIN it on the booking table and filter on the record having NULL matching application_id.

Answer 2

First, I would start by breaking down your query "2008-12-25 00:00:00" to "2008-12-30 00:00:00" into "time periods" of one day each. This is relatively easy with a table variable and a while loop so I won't go into it here.

Then you could loop through each of the time periods from the table variable and see if it overlaps any of the existing bookings (you would only pull out the bookings that overlap the query time period). To do that I suggest using this helper function:

CREATE FUNCTION [dbo].[fn_TimePeriodsOverlap] 
(
    @pStartTP1 datetime,
    @pEndTP1 datetime,
    @pStartTP2 datetime,
    @pEndTP2 datetime
)
RETURNS bit
AS
BEGIN
    DECLARE @Result bit
    SET @Result = 0

    IF @pStartTP1 >= @pStartTP2 AND @pStartTP1 < @pEndTP2
            SET @Result = 1
    ELSE IF @pEndTP1 >= @pStartTP2 AND @pEndTP1 < @pEndTP2
            SET @Result = 1
    ELSE IF @pStartTP2 >= @pStartTP1 AND @pStartTP2 < @pEndTP1
            SET @Result = 1
    ELSE IF @pEndTP2 >= @pStartTP1 AND @pEndTP2 < @pEndTP1
            SET @Result = 1

    RETURN @Result

END

That will return 1 if two time periods overlap, and 0 otherwise. This has the advantage of working even if the booking blocks aren't always a full day.

Answer 3

One way would be to put all dates in the range in a temp table and join with that.

declare @startDate datetime, @endDate datetime

SET @startDate = '2009-05-01' SET @endDate = '2009-05-31'

declare @dates table (date datetime)

insert into @dates values (@startDate)

while @startDate < @endDate begin set @startDate = @startDate + 1

insert into @dates values (@startDate)

end

select d.* from applications a left join @dates d on d.date between a.from and a.to where a.application_id is null

Not tested, but something like that might work.