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! 9  1884 
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.
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.
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.
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.
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"
"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
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"
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"
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" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Brian O'Gorman |
last post by:
I have:
a) a calendar table (tblCalendar) holding consecutive dates.
b) a vacancies table (tblVacancies) holding start and end dates of
vacancies
c) a bookings table (tblBookings holding start...
|
by: amit |
last post by:
I want to find out that if there is a mechanism to find a text inside a C# file and replace it with another string.
I am using DTE to do it, the find proerty does it, the results are getting...
|
by: AMIT PUROHIT |
last post by:
hi,
this is a qry which I m stuck up with
I want to find out that if there is a mechanism to find a
text inside a C# file and replace it with another string.
I am using DTE(EnvDTE) to do it,...
|
by: amit |
last post by:
hi
I have created a tool which does a find and replace thru DTE, now after it is done, it opens up a window, "FIND REACHED THE STARTING POINT OF SEARCH"
I want to disbale this window...
|
by: David T. Ashley |
last post by:
Hi,
Red Hat Enterprise Linux 4.X.
I'm writing command-line PHP scripts for the first time.
I get the messages below. What do they mean? Are these operating system
library modules, or...
|
by: Derek |
last post by:
I am creating an intranet using Visual Web Developer Express Edition.
Everything has been working OK until yesterday when I started getting 62
messages all beginning "Could not find schema...
|
by: jyothi.priya143 |
last post by:
Hi Friends,
Hot Vacancies of testing Engineers is here for u now...
Here U can also get Jobs-Freshers & Experienced, Sample Resumes,
Testing Tutorials, Testing Advices, FAQs, Placement Papers...
|
by: jyothi.priya143 |
last post by:
Hi Friends,
Testing Vacancies for 1+years experience are here for u now...
Here u can upload a soft copy of ur resume in one click...
Here U can also get Jobs-Freshers & Experienced, Sample...
|
by: jyothi.priya143 |
last post by:
Hi Friends,
Latest Job Vacancies are here for u now...
Here u can upload a soft copy of ur resume in one click...
Here U can also get Jobs-Freshers & Experienced, Sample Resumes,
Testing...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
