Availability Search - SQL Server Forums

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

SQL Server Development (2000)

Availability Search

Author

Topic

daddio2005
Starting Member

4 Posts

Posted - 2005-12-08 : 11:40:11

I need to be able to search through a database of property bookings and retreive all properties that are available within the date range specified.

For example:

Arrive: 7 Dec 2005 for 7 Nights.

So availability of a property needs to be: 7 Dec - 14 Dec

I assume I will have a table of property bookings as follows:

BOOKINGS TABLE
PropID PropStart PropEnd PropLength
3 03/12/05 13/12/05 10
5 03/12/05 13/12/05 10
3 15/12/05 28/12/05 13
7 01/01/06 15/01/06 14

Obviously I have another table of property details which relate to the PropID in the above bookings table.

PROPERTY TABLE
PropID PropName
3 My House
5 His House
7 Her House

I want a search box which specifies start date and length (can work out end date from that if required), when submitted returns the details of properties available for this length of time.

I think this will be a tricky one to execute!

Any help on this would be most appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-08 : 19:50:53

is this it ?

create table #BOOKINGS
(	PropID 		int,
	PropStart 	datetime,
	PropEnd 	datetime,
	PropLength	int
)

insert into #BOOKINGS
select 	3, '2005-12-03', '2005-12-13', 10	union all
select	5, '2005-12-03', '2005-12-13', 10	union all
select	3, '2005-12-15', '2005-12-28', 13	union all
select	7, '2005-01-01', '2005-01-15', 14


create table #PROPERTY
(
	PropID 		int,
	PropName	varchar(50)
)

insert into #PROPERTY
select	3, 'My House'	union all
select	5, 'His House'	union all
select	7, 'Her House'

declare
	@req_date	datetime,
	@nights		int

select	@req_date	= '2005-12-07',
	@nights		= 6

select 	*
from	#BOOKINGS b inner join #PROPERTY p
on	b.PropID	= p.PropID
where	PropStart	<= @req_date
and	PropEnd		>= @req_date
and	PropStart	<= dateadd(day, @nights, @req_date)
and	PropEnd		>= dateadd(day, @nights, @req_date)

-----------------
[KH]
Guys, where are we right now ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources