|
 |
asp_databases thread: Room Booking facility in ASP
Message #1 by Craig Flannigan <ckf@k...> on Thu, 19 Apr 01 11:40:36 BST
|
|
Hi,
I'm having trouble building a facility on our Intranet that allows
users to book conference rooms.
I need to be able to view records from a table called Bookings, and
see those bookings which overlap a start and end time that I
provide.
For example, I want to see all bookings between 9am and 11am. But is
also needs to show rooms which were booked from say 8am to 11:15am
or 9:30am to 11am or later. In other words, I need to ensure that
all possibilities are caught.
This recordset of rooms booked during the start and end time that I
provide will then be used against a list of all rooms we have to
produce a hyperlinked list of Available Rooms between the two times.
So far the SQL I've got seems to let in double bookings - which it
mustn't.
Example :
SELECT Booking.BookingID, Booking.BookingStartTime,
Booking.BookingEndTime, Booking.BookingDate, Booking.RoomID,
Booking.BookedBy
FROM Booking
WHERE (((Booking.BookingStartTime)<=#12/30/1899 15:0:0#) AND
((Booking.BookingDate)=#4/19/2001#)) OR
(((Booking.BookingEndTime)>#12/30/1899 15:0:0# And
(Booking.BookingEndTime)<=#12/30/1899 18:0:0#) AND
((Booking.BookingDate)=#4/19/2001#)) OR
(((Booking.BookingStartTime)<#12/30/1899 18:0:0#) AND
((Booking.BookingDate)=#4/19/2001#));
This should show me rooms free between 3PM and 6PM. It actually
brings back all rooms regardless of time.
Please help!!
Regards,
Craig.
Message #2 by "Dallas Martin" <dmartin@z...> on Thu, 19 Apr 2001 07:36:19 -0400
|
|
What is 12/30/1899?
----- Original Message -----
From: "Craig Flannigan" <ckf@k...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, April 19, 2001 7:40 AM
Subject: [asp_databases] Room Booking facility in ASP
> Hi,
>
> I'm having trouble building a facility on our Intranet that allows
> users to book conference rooms.
>
> I need to be able to view records from a table called Bookings, and
> see those bookings which overlap a start and end time that I
> provide.
>
> For example, I want to see all bookings between 9am and 11am. But is
> also needs to show rooms which were booked from say 8am to 11:15am
> or 9:30am to 11am or later. In other words, I need to ensure that
> all possibilities are caught.
>
> This recordset of rooms booked during the start and end time that I
> provide will then be used against a list of all rooms we have to
> produce a hyperlinked list of Available Rooms between the two times.
>
> So far the SQL I've got seems to let in double bookings - which it
> mustn't.
>
> Example :
>
> SELECT Booking.BookingID, Booking.BookingStartTime,
> Booking.BookingEndTime, Booking.BookingDate, Booking.RoomID,
> Booking.BookedBy
> FROM Booking
> WHERE (((Booking.BookingStartTime)<=#12/30/1899 15:0:0#) AND
> ((Booking.BookingDate)=#4/19/2001#)) OR
> (((Booking.BookingEndTime)>#12/30/1899 15:0:0# And
> (Booking.BookingEndTime)<=#12/30/1899 18:0:0#) AND
> ((Booking.BookingDate)=#4/19/2001#)) OR
> (((Booking.BookingStartTime)<#12/30/1899 18:0:0#) AND
> ((Booking.BookingDate)=#4/19/2001#));
>
>
> This should show me rooms free between 3PM and 6PM. It actually
> brings back all rooms regardless of time.
>
> Please help!!
>
> Regards,
> Craig.
>
>
Message #3 by "Eric Van Camp" <eric@a...> on Thu, 19 Apr 2001 13:47:10 +0200
|
|
think you need to add some brackets here!
eric
-----Original Message-----
From: Craig Flannigan [mailto:ckf@k...]
Sent: Thursday, April 19, 2001 1:41 PM
To: ASP Databases
Subject: [asp_databases] Room Booking facility in ASP
Hi,
I'm having trouble building a facility on our Intranet that allows
users to book conference rooms.
I need to be able to view records from a table called Bookings, and
see those bookings which overlap a start and end time that I
provide.
For example, I want to see all bookings between 9am and 11am. But is
also needs to show rooms which were booked from say 8am to 11:15am
or 9:30am to 11am or later. In other words, I need to ensure that
all possibilities are caught.
This recordset of rooms booked during the start and end time that I
provide will then be used against a list of all rooms we have to
produce a hyperlinked list of Available Rooms between the two times.
So far the SQL I've got seems to let in double bookings - which it
mustn't.
Example :
SELECT Booking.BookingID, Booking.BookingStartTime,
Booking.BookingEndTime, Booking.BookingDate, Booking.RoomID,
Booking.BookedBy
FROM Booking
WHERE (((Booking.BookingStartTime)<=#12/30/1899 15:0:0#) AND
((Booking.BookingDate)=#4/19/2001#)) OR
(((Booking.BookingEndTime)>#12/30/1899 15:0:0# And
(Booking.BookingEndTime)<=#12/30/1899 18:0:0#) AND
((Booking.BookingDate)=#4/19/2001#)) OR
(((Booking.BookingStartTime)<#12/30/1899 18:0:0#) AND
((Booking.BookingDate)=#4/19/2001#));
This should show me rooms free between 3PM and 6PM. It actually
brings back all rooms regardless of time.
Please help!!
Regards,
Craig.
Message #4 by Craig Flannigan <ckf@k...> on Thu, 19 Apr 01 12:52:56 BST
|
|
I don't know. I never specified it. It appears that if I only enter
a time in Access, it adds that date.
I cannot remove it as everytime I run it, it gets added.
_____________________________________________________
Craig Flannigan
Development Supervisor - Business Systems
Kaye House
Tel: 0114 256 6070
Email: ckf@k...
----------
>What is 12/30/1899?
>
>
>----- Original Message -----
>From: "Craig Flannigan" <ckf@k...>
>To: "ASP Databases" <asp_databases@p...>
>Sent: Thursday, April 19, 2001 7:40 AM
>Subject: [asp_databases] Room Booking facility in ASP
>
>
>> Hi,
>>
>> I'm having trouble building a facility on our Intranet that
>allows
>> users to book conference rooms.
>>
>> I need to be able to view records from a table called Bookings,
>and
>> see those bookings which overlap a start and end time that I
>> provide.
>>
>> For example, I want to see all bookings between 9am and 11am.
>But is
>> also needs to show rooms which were booked from say 8am to
>11:15am
>> or 9:30am to 11am or later. In other words, I need to ensure
>that
>> all possibilities are caught.
>>
>> This recordset of rooms booked during the start and end time
>that I
>> provide will then be used against a list of all rooms we have to
>> produce a hyperlinked list of Available Rooms between the two
>times.
>>
>> So far the SQL I've got seems to let in double bookings - which
>it
>> mustn't.
>>
>> Example :
>>
>> SELECT Booking.BookingID, Booking.BookingStartTime,
>> Booking.BookingEndTime, Booking.BookingDate, Booking.RoomID,
>> Booking.BookedBy
>> FROM Booking
>> WHERE (((Booking.BookingStartTime)<=#12/30/1899 15:0:0#) AND
>> ((Booking.BookingDate)=#4/19/2001#)) OR
>> (((Booking.BookingEndTime)>#12/30/1899 15:0:0# And
>> (Booking.BookingEndTime)<=#12/30/1899 18:0:0#) AND
>> ((Booking.BookingDate)=#4/19/2001#)) OR
>> (((Booking.BookingStartTime)<#12/30/1899 18:0:0#) AND
>> ((Booking.BookingDate)=#4/19/2001#));
>>
>>
>> This should show me rooms free between 3PM and 6PM. It actually
>> brings back all rooms regardless of time.
>>
>> Please help!!
>>
>> Regards,
>> Craig.
Message #5 by "Dallas Martin" <dmartin@z...> on Thu, 19 Apr 2001 08:05:31 -0400
|
|
I think I would change your query:
SELECT .........
FROM ...........
WHERE Booking.BookingDate BETWEEN @date1 AND @date2
AND Booking.BookingStartTime BETWEEN @time1 AND @time2
AND Booking.BookingEndTime BETWEEN @time1 AND @time2
Of course, this assumes that the @time1 and @time2 cover a sufficiently
large enough range.
But then again, if you need all records for a selected date range then why
not
drop the time range altogether.
Lastly, I suggest that you examine the acutal BookingStartDates and
BookingEndDates
in your table.
These two dates are not the same to SQL. If I had a query which SELECTED
theDate FROM
table WHERE theDate = '4/19/2001', only the second record would be returned.
2001-04-19 07:57:00
2001-04-19 00:00:00
However, If my query were re-stated to SELECT theDate FROM table WHERE
theDate BETWEEN 2001-04-19 00:00:00 AND 2001-04-19 07:57:50, then
both record would be returned.
Dallas Martin
----- Original Message -----
From: "Craig Flannigan" <ckf@k...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, April 19, 2001 7:40 AM
Subject: [asp_databases] Room Booking facility in ASP
> Hi,
>
> I'm having trouble building a facility on our Intranet that allows
> users to book conference rooms.
>
> I need to be able to view records from a table called Bookings, and
> see those bookings which overlap a start and end time that I
> provide.
>
> For example, I want to see all bookings between 9am and 11am. But is
> also needs to show rooms which were booked from say 8am to 11:15am
> or 9:30am to 11am or later. In other words, I need to ensure that
> all possibilities are caught.
>
> This recordset of rooms booked during the start and end time that I
> provide will then be used against a list of all rooms we have to
> produce a hyperlinked list of Available Rooms between the two times.
>
> So far the SQL I've got seems to let in double bookings - which it
> mustn't.
>
> Example :
>
> SELECT Booking.BookingID, Booking.BookingStartTime,
> Booking.BookingEndTime, Booking.BookingDate, Booking.RoomID,
> Booking.BookedBy
> FROM Booking
> WHERE (((Booking.BookingStartTime)<=#12/30/1899 15:0:0#) AND
> ((Booking.BookingDate)=#4/19/2001#)) OR
> (((Booking.BookingEndTime)>#12/30/1899 15:0:0# And
> (Booking.BookingEndTime)<=#12/30/1899 18:0:0#) AND
> ((Booking.BookingDate)=#4/19/2001#)) OR
> (((Booking.BookingStartTime)<#12/30/1899 18:0:0#) AND
> ((Booking.BookingDate)=#4/19/2001#));
>
>
> This should show me rooms free between 3PM and 6PM. It actually
> brings back all rooms regardless of time.
>
> Please help!!
>
> Regards,
> Craig.
Message #6 by Craig Flannigan <ckf@k...> on Thu, 19 Apr 01 13:35:57 BST
|
|
Thanks for your reply.
I changed my SQL to reflex your suggestion, but it only returned the
EXACT match, and not those rooms booked with times that overlap the
times that I searched on.
If I searched for rooms booked between 10am and 11am, I also want
the booked rooms which would overlap those times. Such as a room
booked from 10am until 2pm should also show up, but a room booked
from 8am to 9am should not.
So should I be storing a time of 00:00:00 with the date when I run
the INSERT INTO statement?
Many thanks for your help so far.
Regards,
Craig.
_____________________________________________________
Craig Flannigan
Development Supervisor - Business Systems
Kaye House
Tel: 0114 256 6070
Email: ckf@k...
----------
>I think I would change your query:
>
>SELECT .........
>FROM ...........
>WHERE Booking.BookingDate BETWEEN @date1 AND @date2
>AND Booking.BookingStartTime BETWEEN @time1 AND @time2
>AND Booking.BookingEndTime BETWEEN @time1 AND @time2
>
>Of course, this assumes that the @time1 and @time2 cover a
>sufficiently
>large enough range.
>
>But then again, if you need all records for a selected date range
>then why
>not
>drop the time range altogether.
>
>Lastly, I suggest that you examine the acutal BookingStartDates
>and
>BookingEndDates
>in your table.
>
>These two dates are not the same to SQL. If I had a query which
>SELECTED
>theDate FROM
>table WHERE theDate = '4/19/2001', only the second record would be
>returned.
>
>2001-04-19 07:57:00
>2001-04-19 00:00:00
>
>However, If my query were re-stated to SELECT theDate FROM table
>WHERE
>theDate BETWEEN 2001-04-19 00:00:00 AND 2001-04-19 07:57:50, then
>both record would be returned.
>
>
>Dallas Martin
>
>----- Original Message -----
>From: "Craig Flannigan" <ckf@k...>
>To: "ASP Databases" <asp_databases@p...>
>Sent: Thursday, April 19, 2001 7:40 AM
>Subject: [asp_databases] Room Booking facility in ASP
>
>
>> Hi,
>>
>> I'm having trouble building a facility on our Intranet that
>allows
>> users to book conference rooms.
>>
>> I need to be able to view records from a table called Bookings,
>and
>> see those bookings which overlap a start and end time that I
>> provide.
>>
>> For example, I want to see all bookings between 9am and 11am.
>But is
>> also needs to show rooms which were booked from say 8am to
>11:15am
>> or 9:30am to 11am or later. In other words, I need to ensure
>that
>> all possibilities are caught.
>>
>> This recordset of rooms booked during the start and end time
>that I
>> provide will then be used against a list of all rooms we have to
>> produce a hyperlinked list of Available Rooms between the two
>times.
>>
>> So far the SQL I've got seems to let in double bookings - which
>it
>> mustn't.
>>
>> Example :
>>
>> SELECT Booking.BookingID, Booking.BookingStartTime,
>> Booking.BookingEndTime, Booking.BookingDate, Booking.RoomID,
>> Booking.BookedBy
>> FROM Booking
>> WHERE (((Booking.BookingStartTime)<=#12/30/1899 15:0:0#) AND
>> ((Booking.BookingDate)=#4/19/2001#)) OR
>> (((Booking.BookingEndTime)>#12/30/1899 15:0:0# And
>> (Booking.BookingEndTime)<=#12/30/1899 18:0:0#) AND
>> ((Booking.BookingDate)=#4/19/2001#)) OR
>> (((Booking.BookingStartTime)<#12/30/1899 18:0:0#) AND
>> ((Booking.BookingDate)=#4/19/2001#));
>>
>>
>> This should show me rooms free between 3PM and 6PM. It actually
>> brings back all rooms regardless of time.
>>
>> Please help!!
>>
>> Regards,
>> Craig.
>
>
Message #7 by Craig Flannigan <ckf@k...> on Thu, 19 Apr 01 13:40:27 BST
|
|
Where?!
_____________________________________________________
Craig Flannigan
Development Supervisor - Business Systems
Kaye House
Tel: 0114 256 6070
Email: ckf@k...
----------
>think you need to add some brackets here!
>eric
>
>-----Original Message-----
>From: Craig Flannigan [mailto:ckf@k...]
>Sent: Thursday, April 19, 2001 1:41 PM
>To: ASP Databases
>Subject: [asp_databases] Room Booking facility in ASP
>
>
>Hi,
>
>I'm having trouble building a facility on our Intranet that allows
>users to book conference rooms.
>
>I need to be able to view records from a table called Bookings,
>and
>see those bookings which overlap a start and end time that I
>provide.
>
>For example, I want to see all bookings between 9am and 11am. But
>is
>also needs to show rooms which were booked from say 8am to 11:15am
>or 9:30am to 11am or later. In other words, I need to ensure that
>all possibilities are caught.
>
>This recordset of rooms booked during the start and end time that
>I
>provide will then be used against a list of all rooms we have to
>produce a hyperlinked list of Available Rooms between the two
>times.
>
>So far the SQL I've got seems to let in double bookings - which it
>mustn't.
>
>Example :
>
>SELECT Booking.BookingID, Booking.BookingStartTime,
>Booking.BookingEndTime, Booking.BookingDate, Booking.RoomID,
>Booking.BookedBy
>FROM Booking
>WHERE (((Booking.BookingStartTime)<=#12/30/1899 15:0:0#) AND
>((Booking.BookingDate)=#4/19/2001#)) OR
>(((Booking.BookingEndTime)>#12/30/1899 15:0:0# And
>(Booking.BookingEndTime)<=#12/30/1899 18:0:0#) AND
>((Booking.BookingDate)=#4/19/2001#)) OR
>(((Booking.BookingStartTime)<#12/30/1899 18:0:0#) AND
>((Booking.BookingDate)=#4/19/2001#));
>
>
>This should show me rooms free between 3PM and 6PM. It actually
>brings back all rooms regardless of time.
>
>Please help!!
>
>Regards,
>Craig.
>
>
Message #8 by "Matthew Lohr" <mlohr@t...> on Thu, 19 Apr 2001 08:59:17 -0400
|
|
my thought would to have the app book everything in half hour increments so
an 8:00 am to 10:am booking would also include entries for 8:30 9:00 and
9:30 so if you were to check for a 9:00 to 9:30 meeting you would be covered
-----Original Message-----
From: Dallas Martin [mailto:dmartin@z...]
Sent: Thursday, April 19, 2001 8:06 AM
To: ASP Databases
Subject: [asp_databases] Re: Room Booking facility in ASP
I think I would change your query:
SELECT .........
FROM ...........
WHERE Booking.BookingDate BETWEEN @date1 AND @date2
AND Booking.BookingStartTime BETWEEN @time1 AND @time2
AND Booking.BookingEndTime BETWEEN @time1 AND @time2
Of course, this assumes that the @time1 and @time2 cover a sufficiently
large enough range.
But then again, if you need all records for a selected date range then why
not
drop the time range altogether.
Lastly, I suggest that you examine the acutal BookingStartDates and
BookingEndDates
in your table.
These two dates are not the same to SQL. If I had a query which SELECTED
theDate FROM
table WHERE theDate = '4/19/2001', only the second record would be returned.
2001-04-19 07:57:00
2001-04-19 00:00:00
However, If my query were re-stated to SELECT theDate FROM table WHERE
theDate BETWEEN 2001-04-19 00:00:00 AND 2001-04-19 07:57:50, then
both record would be returned.
Dallas Martin
----- Original Message -----
From: "Craig Flannigan" <ckf@k...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, April 19, 2001 7:40 AM
Subject: [asp_databases] Room Booking facility in ASP
> Hi,
>
> I'm having trouble building a facility on our Intranet that allows
> users to book conference rooms.
>
> I need to be able to view records from a table called Bookings, and
> see those bookings which overlap a start and end time that I
> provide.
>
> For example, I want to see all bookings between 9am and 11am. But is
> also needs to show rooms which were booked from say 8am to 11:15am
> or 9:30am to 11am or later. In other words, I need to ensure that
> all possibilities are caught.
>
> This recordset of rooms booked during the start and end time that I
> provide will then be used against a list of all rooms we have to
> produce a hyperlinked list of Available Rooms between the two times.
>
> So far the SQL I've got seems to let in double bookings - which it
> mustn't.
>
> Example :
>
> SELECT Booking.BookingID, Booking.BookingStartTime,
> Booking.BookingEndTime, Booking.BookingDate, Booking.RoomID,
> Booking.BookedBy
> FROM Booking
> WHERE (((Booking.BookingStartTime)<=#12/30/1899 15:0:0#) AND
> ((Booking.BookingDate)=#4/19/2001#)) OR
> (((Booking.BookingEndTime)>#12/30/1899 15:0:0# And
> (Booking.BookingEndTime)<=#12/30/1899 18:0:0#) AND
> ((Booking.BookingDate)=#4/19/2001#)) OR
> (((Booking.BookingStartTime)<#12/30/1899 18:0:0#) AND
> ((Booking.BookingDate)=#4/19/2001#));
>
>
> This should show me rooms free between 3PM and 6PM. It actually
> brings back all rooms regardless of time.
>
> Please help!!
>
> Regards,
> Craig.
|
|
|
View Cart
