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 = <currentloopdat e> <currentlooptim e>;
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...Cos2Much SpamMakesUFat! 9  1916 
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******@NOyah oo.SPAMcom> wrote in message
news:el******** ******@TK2MSFTN GP09.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******@NOyah oo.SPAMcom> wrote in message
news:el******** ******@TK2MSFTN GP09.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******@NOyah oo.SPAMcom> wrote in message
news:eF******** ******@TK2MSFTN GP09.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******@NOyah oo.SPAMcom> wrote in message
news:el******** ******@TK2MSFTN GP09.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******@NOyah oo.SPAMcom> wrote in message
news:eF******** ******@TK2MSFTN GP09.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******@NOyah oo.SPAMcom> wrote in message
news:el******** ******@TK2MSFTN GP09.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******@NOyah oo.SPAMcom> wrote in message
news:ue******** ******@TK2MSFTN GP15.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(Booki ngID 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.StartTim e)
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.BookingCale ndar
(
BookingTime datetime NOT NULL,
Available bit NULL,
CONSTRAINT PK__BookingCale ndar
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(va rchar(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,@c ur)
END
I got to here:
create view vRoomBookingSlo ts AS
SELECT RoomID,BookingT ime FROM
Rooms Cross Join BookingCalendar
WHERE Available=1
go
declare
@start datetime, @to datetime
set @start='2004011 1'
set @to='20040124'
Select qSlots.roomid, qSlots.bookingt ime
,case when qSlots.bookingt ime between b.fromtime and
dateadd(ms,-300001,b.totime )
THEN 1 ELSE 0 END Booked
from
(select roomid,bookingt ime from vroombookingslo ts
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******@NOyah oo.SPAMcom> wrote in message
news:eF******** ******@TK2MSFTN GP09.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******@NOyah oo.SPAMcom> wrote in message
news:el******** ******@TK2MSFTN GP09.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....A nd 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******@NOyah oo.SPAMcom> wrote in message
news:#q******** *****@TK2MSFTNG P11.phx.gbl... The first thing to do is to create a Calendar table:
CREATE TABLE dbo.BookingCale ndar
(
BookingTime datetime NOT NULL,
Available bit NULL,
CONSTRAINT PK__BookingCale ndar
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(va rchar(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,@c ur)
END
I got to here:
create view vRoomBookingSlo ts AS
SELECT RoomID,BookingT ime FROM
Rooms Cross Join BookingCalendar
WHERE Available=1
go
declare
@start datetime, @to datetime
set @start='2004011 1'
set @to='20040124'
Select qSlots.roomid, qSlots.bookingt ime
,case when qSlots.bookingt ime between b.fromtime and
dateadd(ms,-300001,b.totime )
THEN 1 ELSE 0 END Booked
from
(select roomid,bookingt ime from vroombookingslo ts
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******@NOyah oo.SPAMcom> wrote in message
news:eF******** ******@TK2MSFTN GP09.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******@NOyah oo.SPAMcom> wrote in message
news:el******** ******@TK2MSFTN GP09.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....A nd 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******@NOyah oo.SPAMcom> wrote in message
news:#q******** *****@TK2MSFTNG P11.phx.gbl... The first thing to do is to create a Calendar table:
CREATE TABLE dbo.BookingCale ndar
(
BookingTime datetime NOT NULL,
Available bit NULL,
CONSTRAINT PK__BookingCale ndar
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(va rchar(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,@c ur)
END
I got to here:
create view vRoomBookingSlo ts AS
SELECT RoomID,BookingT ime FROM
Rooms Cross Join BookingCalendar
WHERE Available=1
go
declare
@start datetime, @to datetime
set @start='2004011 1'
set @to='20040124'
Select qSlots.roomid, qSlots.bookingt ime
,case when qSlots.bookingt ime between b.fromtime and
dateadd(ms,-300001,b.totime )
THEN 1 ELSE 0 END Booked
from
(select roomid,bookingt ime from vroombookingslo ts
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******@NOyah oo.SPAMcom> wrote in message
news:eF******** ******@TK2MSFTN GP09.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******@NOyah oo.SPAMcom> wrote in message
> news:el******** ******@TK2MSFTN GP09.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 and end dates of each
booking for a given vacancy.
Can anyone tell me how I can generate, with queries, start and end
dates for the vacant periods of a given vacancy? e.g.
|
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 displayed in a find results window pane , but I m not able to programmatically take the contents of the pane.
DTE.Find.FindWhat = "catch"
DTE.Find.Target = vsFindTarget.vsFindTargetFiles
DTE.Find.MatchCase = False
DTE.Find.MatchWholeWord = False...
|
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, the find proerty does it,
the results are getting displayed in a find results window
pane , but I m not able to programmatically take the
contents of the pane.
|
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 programmatically.
how should i do it
this is a partial code
Dim dsData As DataSe
|
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 something in PHP that I don't have? Do I need to
install more Linux packages? Or adjust PHP in some way?
| | |
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 information for the".
I am using Cassini as the web server on my PCand I can still run my site
from within VWD. Does anyone know what I have done to cause these messages to
appear?
Could not find schema information for the element...
|
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 etc.,
I think this will be helpful to you to develop your career and
searching Job easily in Software Testing.
Hurry Up..!!..!!
All the Best... Have a Nice Day...
|
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 Resumes,
Testing Tutorials, Testing Advices, FAQs, Placement Papers etc.,
I think this will be helpful to you to develop your career and
searching Job easily in Software Testing.
Hurry Up..!!..!!
All the Best... Have a Nice Day...
|
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 Tutorials, Testing Advices, FAQs, Placement Papers etc.,
I think this will be helpful to you to develop your career and
searching Job easily in Software Testing.
Hurry Up..!!..!!
All the Best... Have a Nice Day...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
