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!
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!