0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for committed Christians, experts in Christianity and those interested in learning more

declare @room table (id int, name varchar(50), capacity int);
declare @booking table (id int, roomid int, startdt datetime, 
    enddt datetime, seats int);

insert @room values (1, 'Kitchen', 100);
insert @booking values (1, 1, '2012-01-01 10:00', '2012-01-01 12:00', 10);
insert @booking values (2, 1, '2012-01-01 15:00', '2012-01-01 17:00', 10);
insert @booking values (3, 1, '2012-01-01 11:00', '2012-01-01 16:00', 10);
insert @booking values (4, 1, '2012-01-01 15:00', '2012-01-01 17:00', 10);

-- Parameters for query
declare @roomid int
declare @startdt datetime
declare @enddt datetime

set @roomid = 1
set @startdt = '2012-01-01 9:00'
set @enddt = '2012-01-01 17:00'

; with  hours as
        (
        select  @startdt as dt
        union all
        select  dateadd(hour, 1, dt)
        from    Hours
        where   dateadd(hour, 1, dt) < @enddt
        )
,       hour_capacity as
        (
        select  h.dt
        ,       r.capacity
        ,       sum(b.seats) as seats_used
        from    @room r
        cross join
                hours h
        join    @booking b
        on      b.roomid = r.id
                and h.dt between b.startdt and b.enddt
        where   r.id = @roomid
        group by
                h.dt
        ,       r.capacity
        )
select  capacity - max(seats_used)
from    hour_capacity
group by
        capacity

fork query

edited apr 30 13

created feb 29 12 anonymous