By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,583 Members | 3,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,583 IT Pros & Developers. It's quick & easy.

Find Vacancies

P: n/a
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!
Jul 19 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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

Simon Harris wrote:
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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #2

P: n/a
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
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...
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

Simon Harris wrote:
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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 19 '05 #3

P: n/a
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

Simon Harris wrote:
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
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...
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

Simon Harris wrote:
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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #4

P: n/a
Sorry - I should have detailed that.

Following example shows 3 seperate bookings:

BookingID StartTime EndTime
RoomID
1 11/01/2004 08:30:00 11/01/2004 10:30:00 1
2 15/01/2004 10:00:00 15/01/2004 14:00:00 2
3 21/01/2004 11:30:00 23/01/2004 16:00:00 3

Hopefully here I've demonstrated that a booking can be over more than 1 day,
is always booked by the half hour, and requires 1 row in the bookings table.

I guess when you say 'making the problem more complex' you were meaning that
the 'time slots' are not all in the DB, then later assigned to a booking,
which is right. The bookings table only holds details of the actual
bookings.

Many Thanks,
Simon.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eF**************@TK2MSFTNGP09.phx.gbl...
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

Simon Harris wrote:
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
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...
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

Simon Harris wrote:
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.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 19 '05 #5

P: n/a
Don't worry. It's more difficult, but it's possible. I need to go play with
this for a while. I will get back to you.

Bob Barrows

Simon Harris wrote:
Sorry - I should have detailed that.

Following example shows 3 seperate bookings:

BookingID StartTime EndTime
RoomID
1 11/01/2004 08:30:00 11/01/2004 10:30:00 1 2
15/01/2004 10:00:00 15/01/2004 14:00:00
2 3 21/01/2004 11:30:00 23/01/2004 16:00:00
3
Hopefully here I've demonstrated that a booking can be over more than
1 day, is always booked by the half hour, and requires 1 row in the
bookings table.
I guess when you say 'making the problem more complex' you were
meaning that the 'time slots' are not all in the DB, then later
assigned to a booking, which is right. The bookings table only holds
details of the actual bookings.

Many Thanks,
Simon.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eF**************@TK2MSFTNGP09.phx.gbl...
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

Simon Harris wrote:
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
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...
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

Simon Harris wrote:
> 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.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #6

P: n/a
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:ue**************@TK2MSFTNGP15.phx.gbl...
Don't worry. It's more difficult, but it's possible. I need to go play with this for a while. I will get back to you.

Bob Barrows

Simon Harris wrote:
Sorry - I should have detailed that.

Following example shows 3 seperate bookings:

BookingID StartTime EndTime
RoomID
1 11/01/2004 08:30:00 11/01/2004 10:30:00 1 2
15/01/2004 10:00:00 15/01/2004 14:00:00
2 3 21/01/2004 11:30:00 23/01/2004 16:00:00
3
Hopefully here I've demonstrated that a booking can be over more than
1 day, is always booked by the half hour, and requires 1 row in the
bookings table.
I guess when you say 'making the problem more complex' you were
meaning that the 'time slots' are not all in the DB, then later
assigned to a booking, which is right. The bookings table only holds
details of the actual bookings.


Gap analysis on three (3) bookings to three(3) separate rooms wasn't very
interesting, so I modified the sample data to apply to one room.

CREATE TABLE #Bookings(BookingID INT, StartTime DATETIME, EndTime DATETIME,
RoomID INT)

INSERT INTO #Bookings VALUES
(1,'2004-01-31T08:30:00.000','2004-02-02T10:30:00.000',1)
INSERT INTO #Bookings VALUES
(2,'2004-02-15T10:00:00.000','2004-02-17T14:00:00.000',1)
INSERT INTO #Bookings VALUES
(3,'2004-02-28T11:30:00.000','2004-03-01T16:00:00.000',1)

SELECT
B1.RoomID,
B1.EndTime,
MIN(B2.StartTime)
FROM
#Bookings AS B1
LEFT JOIN
#Bookings AS B2
ON
B1.RoomID = B2.RoomID AND
B1.EndTime < B2.StartTime
GROUP BY
B1.RoomID,
B1.EndTime

DROP TABLE #Bookings
Jul 19 '05 #7

P: n/a
The first thing to do is to create a Calendar table:
CREATE TABLE dbo.BookingCalendar
(
BookingTime datetime NOT NULL,
Available bit NULL,
CONSTRAINT PK__BookingCalendar
PRIMARY KEY CLUSTERED (BookingTime)
ON [PRIMARY]
)
and populate it with this script:
declare @start datetime, @end datetime, @cur datetime
set @start = '20040101'
set @end = '20041231 23:59'
Set @cur = @start
WHILE @cur < @end
BEGIN
IF CAST(CONVERT(varchar(20),@cur,108) AS DATETIME)
= '8:30 AM' INSERT INTO bookingcalendar
VALUES (@cur,1)
ELSE

INSERT INTO bookingcalendar
VALUES (@cur,0)
set @cur = dateadd(n,30,@cur)
END

I got to here:

create view vRoomBookingSlots AS
SELECT RoomID,BookingTime FROM
Rooms Cross Join BookingCalendar
WHERE Available=1

go

declare
@start datetime, @to datetime
set @start='20040111'
set @to='20040124'

Select qSlots.roomid, qSlots.bookingtime
,case when qSlots.bookingtime between b.fromtime and
dateadd(ms,-300001,b.totime)
THEN 1 ELSE 0 END Booked
from
(select roomid,bookingtime from vroombookingslots
where bookingtime >= @start and bookingtime < dateadd(d,1,@to)) qSlots
left join bookings b on qSlots.roomid=b.roomid

I'm kinda tired now after watching the Patriots squeak by the Colts, so I
will resume work on this tomorrow, unless you see where i'm going and you
are able to take it to its conclusion.

Bob Barrows
Simon Harris wrote: Sorry - I should have detailed that.

Following example shows 3 seperate bookings:

BookingID StartTime EndTime
RoomID
1 11/01/2004 08:30:00 11/01/2004 10:30:00 1 2
15/01/2004 10:00:00 15/01/2004 14:00:00
2 3 21/01/2004 11:30:00 23/01/2004 16:00:00
3
Hopefully here I've demonstrated that a booking can be over more than
1 day, is always booked by the half hour, and requires 1 row in the
bookings table.
I guess when you say 'making the problem more complex' you were
meaning that the 'time slots' are not all in the DB, then later
assigned to a booking, which is right. The bookings table only holds
details of the actual bookings.

Many Thanks,
Simon.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eF**************@TK2MSFTNGP09.phx.gbl...
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

Simon Harris wrote:
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
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...
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

Simon Harris wrote:
> 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.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Jul 19 '05 #8

P: n/a
Hi Bob,

Thanks so much for your detailed reply. I kinda see where you are going, but
would very much appreciate it if you could see this through for me. I got to
the point of the two tables, and the view but ran out of time on Friday...I
plan to continue tommorow.

One area I guessed would wanted to confirm was the calendar table - This,
following your query results in 2004's bookings. I guess this would need to
be updated some how, as time goes on...Perhaps some sort of scheduled task
to check the year of the current date, then update the table accordingly,
deleting perhaps dates for years older than 5 years ago too?

Or...Perhaps it would be possible to have two years worth of calendar, but
ignore the years when doing the comparison....And only allow trhe user to do
1 years worth of checking at a time. (Any more would likely render more data
than they would actually use/look at anyway!)

I'll continue with working through your post tommorow and post back how I
get on.

Best regards and thanks again,

Simon.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:#q*************@TK2MSFTNGP11.phx.gbl...
The first thing to do is to create a Calendar table:
CREATE TABLE dbo.BookingCalendar
(
BookingTime datetime NOT NULL,
Available bit NULL,
CONSTRAINT PK__BookingCalendar
PRIMARY KEY CLUSTERED (BookingTime)
ON [PRIMARY]
)
and populate it with this script:
declare @start datetime, @end datetime, @cur datetime
set @start = '20040101'
set @end = '20041231 23:59'
Set @cur = @start
WHILE @cur < @end
BEGIN
IF CAST(CONVERT(varchar(20),@cur,108) AS DATETIME)
>= '8:30 AM'

INSERT INTO bookingcalendar
VALUES (@cur,1)
ELSE

INSERT INTO bookingcalendar
VALUES (@cur,0)
set @cur = dateadd(n,30,@cur)
END

I got to here:

create view vRoomBookingSlots AS
SELECT RoomID,BookingTime FROM
Rooms Cross Join BookingCalendar
WHERE Available=1

go

declare
@start datetime, @to datetime
set @start='20040111'
set @to='20040124'

Select qSlots.roomid, qSlots.bookingtime
,case when qSlots.bookingtime between b.fromtime and
dateadd(ms,-300001,b.totime)
THEN 1 ELSE 0 END Booked
from
(select roomid,bookingtime from vroombookingslots
where bookingtime >= @start and bookingtime < dateadd(d,1,@to)) qSlots
left join bookings b on qSlots.roomid=b.roomid

I'm kinda tired now after watching the Patriots squeak by the Colts, so I
will resume work on this tomorrow, unless you see where i'm going and you
are able to take it to its conclusion.

Bob Barrows
Simon Harris wrote:
Sorry - I should have detailed that.

Following example shows 3 seperate bookings:

BookingID StartTime EndTime
RoomID
1 11/01/2004 08:30:00 11/01/2004 10:30:00 1 2
15/01/2004 10:00:00 15/01/2004 14:00:00
2 3 21/01/2004 11:30:00 23/01/2004 16:00:00
3
Hopefully here I've demonstrated that a booking can be over more than
1 day, is always booked by the half hour, and requires 1 row in the
bookings table.
I guess when you say 'making the problem more complex' you were
meaning that the 'time slots' are not all in the DB, then later
assigned to a booking, which is right. The bookings table only holds
details of the actual bookings.

Many Thanks,
Simon.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eF**************@TK2MSFTNGP09.phx.gbl...
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

Simon Harris wrote:
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
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...
> 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
>
> Simon Harris wrote:
>> 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.
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will
> get a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Jul 19 '05 #9

P: n/a
You may wish to consider Chris's post instead. After I got to a certain
point, I realized that his approach was better.

Bob Barrows
Simon Harris wrote:
Hi Bob,

Thanks so much for your detailed reply. I kinda see where you are
going, but would very much appreciate it if you could see this
through for me. I got to the point of the two tables, and the view
but ran out of time on Friday...I plan to continue tommorow.

One area I guessed would wanted to confirm was the calendar table -
This, following your query results in 2004's bookings. I guess this
would need to be updated some how, as time goes on...Perhaps some
sort of scheduled task to check the year of the current date, then
update the table accordingly, deleting perhaps dates for years older
than 5 years ago too?

Or...Perhaps it would be possible to have two years worth of
calendar, but ignore the years when doing the comparison....And only
allow trhe user to do 1 years worth of checking at a time. (Any more
would likely render more data than they would actually use/look at
anyway!)

I'll continue with working through your post tommorow and post back
how I get on.

Best regards and thanks again,

Simon.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:#q*************@TK2MSFTNGP11.phx.gbl...
The first thing to do is to create a Calendar table:
CREATE TABLE dbo.BookingCalendar
(
BookingTime datetime NOT NULL,
Available bit NULL,
CONSTRAINT PK__BookingCalendar
PRIMARY KEY CLUSTERED (BookingTime)
ON [PRIMARY]
)
and populate it with this script:
declare @start datetime, @end datetime, @cur datetime
set @start = '20040101'
set @end = '20041231 23:59'
Set @cur = @start
WHILE @cur < @end
BEGIN
IF CAST(CONVERT(varchar(20),@cur,108) AS DATETIME)
>= '8:30 AM'

INSERT INTO bookingcalendar
VALUES (@cur,1)
ELSE

INSERT INTO bookingcalendar
VALUES (@cur,0)
set @cur = dateadd(n,30,@cur)
END

I got to here:

create view vRoomBookingSlots AS
SELECT RoomID,BookingTime FROM
Rooms Cross Join BookingCalendar
WHERE Available=1

go

declare
@start datetime, @to datetime
set @start='20040111'
set @to='20040124'

Select qSlots.roomid, qSlots.bookingtime
,case when qSlots.bookingtime between b.fromtime and
dateadd(ms,-300001,b.totime)
THEN 1 ELSE 0 END Booked
from
(select roomid,bookingtime from vroombookingslots
where bookingtime >= @start and bookingtime < dateadd(d,1,@to))
qSlots left join bookings b on qSlots.roomid=b.roomid

I'm kinda tired now after watching the Patriots squeak by the Colts,
so I will resume work on this tomorrow, unless you see where i'm
going and you are able to take it to its conclusion.

Bob Barrows
Simon Harris wrote:
Sorry - I should have detailed that.

Following example shows 3 seperate bookings:

BookingID StartTime EndTime
RoomID
1 11/01/2004 08:30:00 11/01/2004 10:30:00
1 2 15/01/2004 10:00:00 15/01/2004 14:00:00
2 3 21/01/2004 11:30:00 23/01/2004
16:00:00 3
Hopefully here I've demonstrated that a booking can be over more
than 1 day, is always booked by the half hour, and requires 1 row
in the bookings table.
I guess when you say 'making the problem more complex' you were
meaning that the 'time slots' are not all in the DB, then later
assigned to a booking, which is right. The bookings table only holds
details of the actual bookings.

Many Thanks,
Simon.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eF**************@TK2MSFTNGP09.phx.gbl...
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

Simon Harris wrote:
> 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
>
>
> "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
> news:el**************@TK2MSFTNGP09.phx.gbl...
>> 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
>>
>> Simon Harris wrote:
>>> 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.
>>
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my
>> From header is my spam trap, so I don't check it very often. You
>> will
>> get a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get
a quicker response by posting to the newsgroup.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.