Tutorial :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: How to implement check availability in hotel reservation system


Solution:1

One way could be to put the date range in a table variable and join.

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.


Solution:2

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.


Solution:3

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »