How can I find a value that doesn't exist in a table?

Question

I have a simplified table called Bookings that has two columns BookDate and BookSlot. The BookDate column will have dates only (no time) and the BookSlot column will contain the time of the day in intervals of 30 minutes from 0 to 1410 inclusive. (i.e. 600 = 10:00am)

How can I find the first slot available in the future (not booked) without running through a loop?

Here is the table definition and test data:

Create Table Bookings(
    BookDate DateTime Not Null,
    BookSlot Int Not Null
)
Go

Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',0);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',30);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',60);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',630);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-02',60);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-02',90);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-02',120);

I want a way to return the first available slot that is not in the table and that is in the future (based on server time).

Based on above test data:

• If the current server time was 1st Jul, 00:10am, the result should be 1st Jul, 90min (01:30am).
• If the current server time was 2nd Jul, 01:05am, the result should be 2nd Jul, 150min (02:30am).

If there are no bookings in the future, the function would simply return the closest half-hour in the future.

--

SQL Fiddle for this is here:

http://sqlfiddle.com/#!6/0e93d/1

Answer 1

Below is one method that will allow bookings up to 256 days in the future, and allow for an empty Booking table. I assume you are using SQL Server 2005 since your BookDate is dateTime instead of date.
In any case, you might consider storing the slots as a complete datetime instead of separate columns. That will facilitate queries and improve performance.

DECLARE @now DATETIME = '2014-07-01 00:10:00'; 

WITH T4 
     AS (SELECT N 
         FROM   (VALUES(0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0)) AS t(N)), 
     T256 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT 0)) - 1 AS n 
         FROM   T4 AS a 
                CROSS JOIN T4 AS b 
                CROSS JOIN T4 AS c), 
     START_DATE 
     AS (SELECT Dateadd(DAY, Datediff(DAY, '', @now), '') AS start_date), 
     START_TIME 
     AS (SELECT Dateadd(MINUTE, Datediff(MINUTE, '', @now) / 30 * 30, '') AS 
                start_time), 
     DAILY_INTERVALS 
     AS (SELECT N * 30 AS interval 
         FROM   T256 
         WHERE  N < 48) 
SELECT TOP (1) Dateadd(DAY, future_days.N, START_DATE) AS BookDate, 
               DAILY_INTERVALS.INTERVAL                AS BookSlot 
FROM   START_DATE 
       CROSS APPLY START_TIME 
       CROSS APPLY DAILY_INTERVALS 
       CROSS APPLY T256 AS future_days 
WHERE  Dateadd(MINUTE, DAILY_INTERVALS.INTERVAL, 
              Dateadd(DAY, future_days.N, START_DATE)) > START_TIME 
       AND NOT EXISTS(SELECT * 
                      FROM   DBO.BOOKINGS 
                      WHERE  BOOKDATE = START_DATE 
                             AND BOOKSLOT = DAILY_INTERVALS.INTERVAL) 
ORDER  BY BOOKDATE, 
          BOOKSLOT; 

See this SQL Fiddle

Answer 2

It's a bit complicated but try this:

WITH DATA 
     AS (SELECT *, 
                Row_number() 
                  OVER ( 
                    ORDER BY BOOKDATE, BOOKSLOT) RN 
         FROM   BOOKINGS) 
SELECT CASE 
         WHEN T.BOOKSLOT = 1410 THEN Dateadd(DAY, 1, BOOKDATE) 
         ELSE BOOKDATE 
       END Book_Date, 
       CASE 
         WHEN T.BOOKSLOT = 1410 THEN 0 
         ELSE BOOKSLOT + 30 
       END Book_Slot 
FROM   (SELECT TOP 1 T1.* 
        FROM   DATA T1 
               LEFT JOIN DATA t2 
                      ON t1.RN = T2.RN - 1 
        WHERE  t2.BOOKSLOT - t1.BOOKSLOT > 30 
                OR ( t1.BOOKDATE != T2.BOOKDATE 
                     AND ( t2.BOOKSLOT != 0 
                            OR t1.BOOKSLOT != 630 ) ) 
                OR t2.BOOKSLOT IS NULL)T 

Here is the SQL fiddle example.

Explanation

This solution contains 2 parts:

1. Comparing each line to the next and checking for a gap (can be done easier in SQL 2012)
2. Adding a half an hour to create the next slot, this includes moving to the next day if needed.

Edit

Added TOP 1 in the query so that only the first slot is returned as requested.

Update

Here is the updated version including 2 new elements (getting current date+ time and dealing with empty table):

DECLARE @Date DATETIME = '2014-07-01', 
        @Slot INT = 630 
DECLARE @time AS TIME = Cast(Getdate() AS TIME) 

SELECT @Slot = Datepart(HOUR, @time) * 60 + Round(Datepart(MINUTE, @time) / 30, 
                                            0) * 30 
                      + 30 

SET @Date = Cast(Getdate() AS DATE)


;WITH DATA 
     AS (SELECT *, 
                Row_number() 
                  OVER ( 
                    ORDER BY BOOKDATE, BOOKSLOT) RN 
         FROM   BOOKINGS 
         WHERE  BOOKDATE > @Date 
                 OR ( BOOKDATE = @Date 
                      AND BOOKSLOT >= @Slot )) 
SELECT TOP 1 BOOK_DATE, 
             BOOK_SLOT 
FROM   (SELECT CASE 
                 WHEN RN = 1 
                      AND NOT (@slot = BOOKSLOT 
                      AND @Date = BOOKDATE) THEN @Date 
                 WHEN T.BOOKSLOT = 1410 THEN Dateadd(DAY, 1, BOOKDATE) 
                 ELSE BOOKDATE 
               END Book_Date, 
               CASE 
                 WHEN RN = 1 
                      AND NOT (@slot = BOOKSLOT 
                      AND @Date = BOOKDATE) THEN @Slot 
                 WHEN T.BOOKSLOT = 1410 THEN 0 
                 ELSE BOOKSLOT + 30 
               END Book_Slot, 
               1   AS ID 
        FROM   (SELECT TOP 1 T1.* 
                FROM   DATA T1 
                       LEFT JOIN DATA t2 
                              ON t1.RN = T2.RN - 1 
                WHERE  t2.BOOKSLOT - t1.BOOKSLOT > 30 
                        OR ( t1.BOOKDATE != T2.BOOKDATE 
                             AND ( t2.BOOKSLOT != 0 
                                    OR t1.BOOKSLOT != 1410 ) ) 
                        OR t2.BOOKSLOT IS NULL)T 
        UNION 
        SELECT @date AS bookDate, 
               @slot AS BookSlot, 
               2     ID)X 
ORDER  BY X.ID 

Play around with the SQL fiddle and let me know what you think.