Find Vacancies

S

Simon Harris

Hi All,

Ok - I'll confess from the start, this is more about application logic that
ASP, being an ASP programmer, I guessed you people might be able to help! :)

I have built a room bookings system, based on ASP/SQL Server. Bookings are
stored in a table, which holds start date+time and End date+time, amougst
other info. Bookings can run from 8:30 - midnight, and are always made in 30
min blocks (e.g. 14:30 - 15:30 etc)

I now need to devise a page/script that will search for vacancies, so the
administrator of the system can be on the phone, and enter two dates, then
find a vacant spot for a client.

So far, I have done this...

a) Take two dates, user defined
b) Loop dates, incrementing day until end date is matched
c) Within each date, loop again - incrementing minutes by 30 each time
d) for each loop of 30 mins, run SQL statement ... Select * from bookings
where startdate = <currentloopdate> <currentlooptime>;
e) If booking found, slot not available, else available.

This appears to work, but it doesnt take much math to work out that a check
over a 30 day span, requires 930 seperate SQL Select statements!!

As you may expect...its SLOW and draining server resources!

Has anyone got any better ideas on how I can achive this?

Cheers!
Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
 
B

Bob Barrows [MVP]

Given a little more info about your database structure, and how you wish to
display the results, I'm sure we could devise a single sql statement to give
you what you want.

Bob Barrows
 
S

Simon Harris

Hi Bob,

Thanks for your reply, hopefully the following will help.
The system is somewhat more complex that this, but I think I have provided
relevent info for this particular problem.

Regards,
Simon.

bookings table
---------------
FromTime (DateTime)
ToTime (DateTime)
RoomID (Int) - Foreign key joining to Rooms Table (Info such as capacity
etc)

Results should be displayed as follows (In the instance where the user has
opted to check for vacancies between 01 - 05 Jan 04

RoomName1
01/01/2004
Vacancy: 8:30 - 12:00
Vacancy: 13:30 - 16:00
02/01/2004
Vacancy: 15:00 - 18:00
Vacancy: 19:30 - 21:30
03/01/2004
04/01/2004
Vacancy: 18:00 - 19:00
05/01/2004

RoomName2
01/01/2004
02/01/2004
Vacancy: (You get the idea!)
Vacancy: (You get the idea!)
Vacancy: (You get the idea!)
03/01/2004
04/01/2004
Vacancy: (You get the idea!)
Vacancy: (You get the idea!)
Vacancy: (You get the idea!)
Vacancy: (You get the idea!)
05/01/2004

RoomName3
01/01/2004
02/01/2004
Vacancy: (You get the idea!)
Vacancy: (You get the idea!)
03/01/2004
Vacancy: (You get the idea!)
Vacancy: (You get the idea!)
Vacancy: (You get the idea!)
Vacancy: (You get the idea!)
04/01/2004
Vacancy: (You get the idea!)
05/01/2004
 
B

Bob Barrows [MVP]

Just a little more information about how the data is stored (I should have
asked for a few rows of sample data .. ). You said bookings " ... are always
made in 30 min blocks (e.g. 14:30 - 15:30 etc)" Does that mean this
particular booking would be stored in two rows?

14:30 15:00 1
15:00 15:30 1

Or would it be contained in a single row (making the problem more complex)?

14:30 15:30 1

Bob Barrows
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top