Date and Time conflict checking - room reservation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date and Time conflict checking - room reservation

Author

Topic

rladva
Starting Member

1 Post

Posted - 2002-08-20 : 15:21:27

I've been mucking about this thought for a couple of days - and well, here I am -- I'm sure to many of you it's trivial. ;)

I am setting up a room reservation system with the following tables (and columns):
room(id_room[pk], rLocation, rRmNum)
reservation(id_reservation[pk], id_room, datetime_start, datetime_end, capacity, amenities)

I am trying to check for all available time slots (use of SELECT) given the parameters of any/all of the following:
capacity
location
amenities
and required of datetime start and end

Any suggestions to straighten out my thinking are also appreciated! ;)

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-21 : 00:42:19

quote:

I'm sure to many of you it's trivial. ;)

wish it was...I really wish it was...Actually I've just implemented something similar, a resource booking system...

Here's a procedure I created (you'll need to hack it).

It will return all the days of a given month and a -1 for available and a 0 for not available. (You can only book for entire days and not parts of days)...I use it as the basis of a calendar pop-up for resource booking.

The user types in the Type, Subtype and number required (or a specific assetnumber if they're so inclined), and the calendar shows which days are available for booking.

Also, this version tries to share around the booking so that it's not always the same resource being booked out again and again.

I assume in your case the number required will normally be 1 (ie 1 room).

I haven't had a chance to clean up the code yet, so feel free to ask questions.
----------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spLibraryCalendar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spLibraryCalendar]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE procedure dbo.spLibraryCalendar
@datestr nvarchar(50),
@type nvarchar(50),
@subtype nvarchar(50),
@number int,
@assetnumber nvarchar(50)
as

set nocount on
declare @date datetime
set @date = dbo.fx_FDOM(convert(datetime, @datestr))

--create table of date for the month
declare @daysinmonth int
set @daysinmonth = datediff(day, @date, dbo.fx_LDOM(@date)) + 1

declare @counter int
set @counter = 0

declare @dates table (date datetime)

while @counter < @daysinmonth
begin
insert @dates values (dateadd(day,@counter,@date))
set @counter = @counter + 1
end

declare @result table (date datetime, OK int)

--check for supply of asset number
if (isNull(@assetnumber,'')='')
BEGIN
--check for supply or not of subtype
if (IsNull(@subtype,'')='')
BEGIN
--now attempt to find dates which fit request
insert into @result (date, OK)
select date, -1
from @dates d
where ((select count(*) from tLIBRARY_STOCK where type = @type and Active = -1)
- (select count(*) from tLIBRARY_BOOKINGS b, tLIBRARY_STOCK a where b.assetnumber = a.assetnumber and a.type = @type
and b.returned = 0 and b.cancelled = 0
and a.active = -1
and d.date between b.startdate and case when b.startdate < getdate() and b.enddate < getdate() then getdate() else b.enddate end)) >= @number
order by date
END
else
BEGIN
--now attempt to find dates which fit request
insert into @result (date, OK)
select date, -1
from @dates d
where ((select count(*) from tLIBRARY_STOCK where type = @type and subtype = @subtype and Active = -1)
- (select count(*) from tLIBRARY_BOOKINGS b, tLIBRARY_STOCK a where b.assetnumber = a.assetnumber and a.type = @type and a.subtype = @subtype
and b.returned = 0 and b.cancelled = 0
and a.active = -1
and d.date between b.startdate and case when b.startdate < getdate() and b.enddate < getdate() then getdate() else b.enddate end)) >= @number
order by date
END
END
else
BEGIN
--only looking for one specific asset
set @number = 1
--now attempt to find dates which fit request
insert into @result (date, OK)
select date, -1
from @dates d
where ((select count(*) from tLIBRARY_STOCK where assetnumber = @assetnumber and Active = -1)
- (select count(*) from tLIBRARY_BOOKINGS b, tLIBRARY_STOCK a where b.assetnumber = a.assetnumber and a.assetnumber = @assetnumber
and b.returned = 0 and b.cancelled = 0
and a.active = -1
and d.date between b.startdate and case when b.startdate < getdate() and b.enddate < getdate() then getdate() else b.enddate end)) >= @number
order by date
END

--and now add fail dates
insert into @result (date, OK)
select a.date , 0
from @dates a left join @result b on a.date = b.date
where b.date is null
--select * from #result

select OK from @result order by date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------------------------------

Hope that helps

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-21 : 01:01:08

Here's the procedure to actually book a resource.

It returns two recordsets, the first is either 0 or -1 for success/failure. The second recordset returns either the resources which have been booked, OR, in the case of failure, all the resources which could be booked matching the criteria (ie you can't have three but you can have these two...)

again - haven't cleaned up the code yet, so ask away:
----------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spBookLibraryItems]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spBookLibraryItems]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure dbo.spBookLibraryItems
@startdatestr nvarchar(50),
@enddatestr nvarchar(50),
@type nvarchar(50),
@subtype nvarchar(50),
@number int,
@assetnumber nvarchar(50),
@namecode nvarchar(10)
as

set nocount on
declare @startdate datetime
declare @enddate datetime
set @startdate = dbo.fx_StartOfDay(convert(datetime, @startdatestr))
set @enddate = dbo.fx_EndofDay(convert(datetime, @enddatestr))

--find all matching assets which are already booked out for this period
declare @booked_out table (assetnumber nvarchar(50))

declare @counter datetime
set @counter = @startdate

if (@assetnumber <> '')
set @number = 1

while @counter <= @enddate
begin
if (@assetnumber ='')
begin
if (@subtype = '')
begin
--type only
insert into @booked_out (assetnumber)
select b.assetnumber
from tLIBRARY_STOCK a, tLIBRARY_BOOKINGS b
where a.assetnumber = b.assetnumber
and a.type = @type
and b.returned = 0 and b.cancelled = 0
and a.active = -1
and @counter between b.startdate and
case when b.startdate < getdate() and b.enddate < getdate() then getdate()
else b.enddate end
end
else
begin
--subtype only
insert into @booked_out (assetnumber)
select b.assetnumber
from tLIBRARY_STOCK a, tLIBRARY_BOOKINGS b
where a.assetnumber = b.assetnumber
and a.type = @type
and a.subtype = @subtype
and b.returned = 0 and b.cancelled = 0
and a.active = -1
and @counter between b.startdate and
case when b.startdate < getdate() and b.enddate < getdate() then getdate()
else b.enddate end
end
end
else
begin
--specific asset
insert into @booked_out (assetnumber)
select b.assetnumber
from tLIBRARY_STOCK a, tLIBRARY_BOOKINGS b
where a.assetnumber = b.assetnumber
and a.assetnumber = @assetnumber
and b.returned = 0 and b.cancelled = 0
and a.active = -1
and @counter between b.startdate and
case when b.startdate < getdate() and b.enddate < getdate() then getdate()
else b.enddate end
end

set @counter = dateadd(day, 1, @counter)
end

declare @provisional table (id int identity(1,1), assetnumber nvarchar(50))
declare @ranking table (assetnumber nvarchar(50), ranking int, type nvarchar(50), subtype nvarchar(50) , active int)

--rank remaining assets according to how many times they've been previously booked out
insert into @ranking (assetnumber, ranking, type, subtype, active)
select a.assetnumber, b.countofbookings, a.type, a.subtype, a.active
from tLIBRARY_STOCK a left join (select assetnumber, count(*) as countofbookings from tLIBRARY_BOOKINGS group by assetnumber) b
on a.assetnumber =b.assetnumber
order by b.countofbookings

--assign assets to provisional booking table
if (@assetnumber = '')
begin
if (@subtype = '')
begin
--type only
insert into @provisional (assetnumber)
select a.assetnumber from @ranking a left join @booked_out b
on a.assetnumber = b.assetnumber
where b.assetnumber is null
and a.type =@type and a.active = -1
order by a.ranking
end
else
begin
--type and subtype
insert into @provisional (assetnumber)
select a.assetnumber from @ranking a left join @booked_out b
on a.assetnumber = b.assetnumber
where b.assetnumber is null
and a.type =@type and a.subtype = @subtype and a.active = -1
order by a.ranking
end
end
else
begin
--specific asset
insert into @provisional (assetnumber)
select a.assetnumber from @ranking a left join @booked_out b
on a.assetnumber = b.assetnumber
where b.assetnumber is null
and a.assetnumber =@assetnumber and a.active = -1
order by a.ranking
end

--now book or return items which could be booked
if ((select count(*) from @provisional) >= @number)
BEGIN
select -1
-- and complete booking
insert into tLIBRARY_BOOKINGS (assetnumber, startdate, enddate, namecode, returned, cancelled)
select a.assetnumber, @startdate, dbo.fx_EndOfDay(@enddate), @namecode, 0,0
from @provisional a left join tLIBRARY_STOCK b
on a.assetnumber = b.assetnumber
where a.id <= @number

select assetnumber from @provisional where id <= @number order by assetnumber
END
else
BEGIN
select 0
--and return assets which matched
select assetnumber from @provisional order by assetnumber
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-08-21 : 05:13:33

you may also wish to follow the following link.....it's a (long and old (in SQLTeam age) discussion on a 'room reservations' problem....may be a more complicated problem than yours, but if you follow it ....it may be enlightening and prevent you getting knotted up if the scope of your problem expands....

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13866

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources