How to select the time a room is available given date and room in SQL Server?

Question

I have 'Reservation' table with the following fields

ReservationID   int
ReservationDateFrom datetime
ReservationDateTo   datetime
ReservationRoom int

I want to create an sp that can select available room and the time it is available given date and room. This is What I have manage to do so far and it doesn't work. It doesn't return anything if there are no room schedule on the given date and it only returns available room schedules between 2 datetime ranges.

CREATE PROCEDURE [dbo].[sp_GetAvailableSchedules](
@date date,
@room int
)
AS 
BEGIN
    select ReservationID, ReservationDateFrom, ReservationDateTo, ReservationRoom 
    from Reservation r
    where ReservationRoom = @room and  @date = CONVERT(date, ReservationDateFrom)
    union all
        select NULL, ReservationDateTo,
               lead(ReservationDateFrom) over (partition by ReservationRoom order by ReservationDateFrom),
               ReservationRoom
        from reservation r
        where ReservationRoom = @room and @date = CONVERT(date, ReservationDateFrom)
END

Sample Data:

ReservationID     ReservationDateFrom        ReservationDateTo       ReservationRoom     
      1         2017-01-02 00:00:00.000    2017-01-02 02:00:00.000         14              
      2         2017-01-02 04:00:00.000    2017-01-02 05:00:00.000         14              
      3         2017-01-02 06:00:00.000    2017-01-02 08:00:00.000         14              
      4         2017-01-02 08:30:00.000    2017-01-02 09:30:00.000         14              
      5         2017-01-02 09:50:00.000    2017-01-02 11:00:00.000         14  
      6         2017-01-02 13:00:00.000    2017-01-02 15:00:00.000         14         

Expected output upon executing

EXEC sp_GetAvailableSchedules '2017-01-02', 14

TimeIn      TimeOut        Minutes
 02:00       04:00           120
 05:00       06:00            60
 08:00       08:30            30
 09:30       09:50            20
 11:00       13:00           120
 15:00       24:00           540

Solutions for sql server 2012 below will do but it would be better if it would work on atleast sql server 2008.

Progress update

I have tried integrating @zerox981's answer into my sp like so

create PROCEDURE [dbo].[sp_GetAvailableSchedules](
@date datetime,
@room int
)
AS 
BEGIN

 with data as
 (
    SELECT *,
        ISNULL(
            (select top 1 ReservationDateFrom from Reservation where ReservationRoom =a.ReservationRoom and ReservationDateFrom> a.ReservationDateTo order by ReservationDateFrom)
            , @date+1) as next 
    from Reservation a
    where ReservationRoom = @room
 )
 select
    cast(Reservationdateto as time) TimeIn,
    cast(next as time) [TimeOut], 
    datediff(mi,ReservationDateTo, next) [Minutes]
 from data

END

I found a lot of problems with this if this is my inserted data

select * from Reservation

ReservationID ReservationDateFrom       ReservationDateTo      ReservationRoom
34             2017-02-17 13:00:00.000  2017-02-17 15:00:00.000   6003
35             2017-02-17 09:00:00.000  2017-02-17 12:00:00.000   6003
36             2017-02-18 12:00:00.000  2017-02-18 14:00:00.000   6003

Case 1 - When there are many reservations within the date and room

declare @date datetime = '2017-02-17 00:00:00.000' , @room int = 6003
exec [sp_GetAvailableSchedules] @date, @room

Expected result

TimeIn TimeOut Minutes
00:00   09:00  540
12:00   13:00   60
15:00   24:00  540 

Actual results

 TimeIn               TimeOut           Minutes
 15:00:00.0000000   12:00:00.0000000    1260
 12:00:00.0000000   13:00:00.0000000    60
 14:00:00.0000000   00:00:00.0000000    -840

Case 2 - When there is 1 reservation within the date and room

declare @date datetime = '2017-02-18 00:00:00.000' , @room int = 6003
exec [sp_GetAvailableSchedules] @date, @room

Expected results

TimeIn TimeOut Minutes
00:00   12:00  720
14:00   24:00  600

Actual results

 TimeIn               TimeOut           Minutes
15:00:00.0000000    12:00:00.0000000    1260
12:00:00.0000000    13:00:00.0000000    60
14:00:00.0000000    00:00:00.0000000    600

Case 3 - When no reservation are in the given date and room(whole day should be available)

declare @date datetime = '2017-02-20 00:00:00.000' , @room int = 500
exec [sp_GetAvailableSchedules] @date, @room

Expected results(whole day available)

TimeIn TimeOut Minutes
00:00   24:00  1440

Actual result is empty

Answer 1

I think you have an error in the test data or in the expected output. Also the edgecases are not defined etc.

Here is something you can start with

if object_id('tempdb..#r') is not null
    drop table #r
create table #r ( ReservationID int ,   ReservationDateFrom datetime   ,     ReservationDateTo  datetime,    ReservationRoom  int)

--insert into #r values(1,         '2017-01-02 00:00:00.000'    ,'2017-01-02 02:00:00.000' ,       14   )           
insert into #r values(2,         '2017-01-02 04:00:00.000'    ,'2017-01-02 05:00:00.000' ,        14  )            
insert into #r values(3,         '2017-01-02 06:00:00.000'    ,'2017-01-02 08:00:00.000' ,        14  )            
insert into #r values(4,         '2017-01-02 08:30:00.000'    ,'2017-01-02 09:30:00.000' ,        14  )            
insert into #r values(5,         '2017-01-02 09:50:00.000'    ,'2017-01-02 11:00:00.000' ,        14  )
insert into #r values(6,         '2017-01-02 13:00:00.000'    ,'2017-01-02 15:00:00.000' ,        14  )   

declare @dt datetime ='2017/01/02', @room int = 14

; with data as
(
    select ReservationDateFrom, ReservationDateTo,ReservationRoom
    from #r
    where ReservationRoom = @room
        and ReservationDateFrom between @dt and @dt+1
    union 
    select @dt,@dt,@room
    union
    select @dt+1,@dt+1,@room
)
,mid as
(
    select *,
        (select top 1 ReservationDateFrom 
        from data 
        where ReservationRoom =a.ReservationRoom 
            and ReservationDateFrom> a.ReservationDateTo 
        order by ReservationDateFrom) [next]
    from data a         
 )
 select
    cast(Reservationdateto as time) TimeIn,
    cast(next as time) [TimeOut], 
    datediff(mi,ReservationDateTo, next) [Minutes]
 from mid
 where datediff(mi,ReservationDateTo, next)>0

You can test it here: http://rextester.com/IZH14294

Answer 2

Assuming no overlaps, and minutes as the smallest time unit, and of course the Numbers table, for SQL Server 2012 this should work:

declare @date datetime = '20170102', @room int = 14
;with x as (
    select num.n, isnull(lag(num.n) over(order by num.n),-1) n_prev
    from utility.numbers num
    left join Reservation t on num.n between datediff(minute, @date, reservationdatefrom) and datediff(minute, @date, reservationdateto) and t.reservationroom = @room
    where num.n < 1440 and num.n > 0
    and t.reservationid is null
)
select *, cast(dateadd(minute, n-1, @date) as time), cast(dateadd (minute, isnull(lead(n_prev+1) over(order by n), 1440), @date) as time),
isnull(lead(n_prev+1) over(order by n), 1440) - (n-1)
from x 
where n <> n_prev+1

(A solution for 2008 would include self-joins. Very ugly IMO).

For more on Numbers table, check here:

https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

Answer 3

I added some example data into the table and fixed the dates. This is not a sophisticated solution, but hopefully it gives you some ideas on how to get the results you want.

You can manage the results with a loop or a common table expression. I used a cte along with a declared table. You can get rid of the declared table in my solution and join the recursive part to a similar select as the base query is, if you want.

The basic idea is to get the ending time of a reservation and the starting time of the next one on the same row.

I only did the select part, I'll leave making it into a procedure for you.

CREATE TABLE reservation (
    ReservationID int,
    ReservationDateFrom datetime,
    ReservationDateTo datetime,
    ReservationRoom int
)

INSERT INTO reservation
(
    ReservationID, ReservationDateFrom, ReservationDateTo, ReservationRoom
)
VALUES
(1, '2017-01-02 00:00:00.000', '2017-01-02 02:00:00.000', 14),              
(2, '2017-01-02 04:00:00.000', '2017-01-02 05:00:00.000', 14),              
(3, '2017-01-02 06:00:00.000', '2017-01-02 08:00:00.000', 14),              
(4, '2017-01-02 08:30:00.000', '2017-01-02 09:30:00.000', 14),              
(5, '2017-01-02 09:50:00.000', '2017-01-02 11:00:00.000', 14),  
(6, '2017-01-02 13:00:00.000', '2017-01-03 15:00:00.000', 14),
(6, '2017-01-03 16:00:00.000', '2017-01-03 17:00:00.000', 14),
(7, '2017-01-04 13:00:00.000', '2017-01-03 15:00:00.000', 14),
(8, '2017-01-02 13:00:00.000', '2017-01-03 15:00:00.000', 15)


DECLARE @date date = '2017-01-02';
DECLARE @room int = 14;
DECLARE @res_table TABLE
(
    RowNumber int,
    IsReserved bit,
    ReservationDateFrom datetime,
    ReservationDateTo datetime
);

INSERT INTO @res_table
SELECT
    ROW_NUMBER() OVER(PARTITION BY ReservationRoom ORDER BY ReservationDateFrom) as RowNumber,
    1 as IsReserved,
    ReservationDateFrom,
    ReservationDateTo
FROM reservation
WHERE @date BETWEEN CAST(ReservationDateFrom as date) AND CAST(ReservationDateTo as date)
        AND @room = ReservationRoom;

WITH cte AS
(
    SELECT
        RowNumber,
        IsReserved,
        ReservationDateFrom,
        ReservationDateTo
    FROM @res_table

    UNION ALL

    SELECT
        cte.RowNumber * -1 as RowNumber,
        cast(0 as bit) as IsReserved,
        cte.ReservationDateTo as ReservationDateFrom,
        rt.ReservationDateFrom as ReservationDateTo
    FROM cte
    INNER JOIN @res_table rt ON
        rt.RowNumber = cte.RowNumber + 1

)
SELECT
    cte.ReservationDateFrom as FreeFromDate,
    cte.ReservationDateTo as FreeToDate,
    DATEDIFF(mi, cte.ReservationDateFrom, cte.ReservationDateTo) as FreeMinutes
FROM cte
WHERE IsReserved = 0
ORDER BY FreeFromDate