A user can search for hall availability by specifying a start datetime and end datetime.. the query should pick and show whether the hall is available for that time..
If the time slot is NOT available the query should pick available slots with same duration for that day and show to the user.
If the time slot is NOT available the query should pick available slots with same duration for that day and show to the user.
Hi Anz,
Please elaborate a bit? I think we can list the booking statuses to let users choose available slots.
Please refer to the following sample:
1. Create tables and insert sample data
CREATE TABLE Halls (
hallid INT IDENTITY ( 1 , 1 ),
hallname NVARCHAR(50))
CREATE TABLE Hallbooking (
hallbookingid INT IDENTITY ( 1 , 1 ),
hallid INT,
startdatetime DATETIME,
enddatetime DATETIME)
INSERT INTO Halls
VALUES ('Hall1')
INSERT INTO Halls
VALUES ('Hall2')
INSERT INTO Halls
VALUES ('Hall3')
INSERT INTO Halls
VALUES ('Hall4')
INSERT INTO Hallbooking
VALUES (1,
'2009-10-26 7:00:00',
'2009-10-26 11:00:00')
INSERT INTO Hallbooking
VALUES (2,
'2009-10-26 9:00:00',
'2009-10-26 12:00:00')
INSERT INTO Hallbooking
VALUES (3,
'2009-10-26 13:00:00',
'2009-10-26 14:00:00')
INSERT INTO Hallbooking
VALUES (1,
'2009-10-27 5:00:00',
'2009-10-26 6:00:00')
INSERT INTO Hallbooking
VALUES (2,
'2009-10-27 6:00:00',
'2009-10-26 7:00:00')
2. Create a Numbers table
SELECT TOP 1024 IDENTITY(INT,0,1) AS N INTO Numbers FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e
ALTER TABLE dbo.Numbers ADD CONSTRAINT
PK_Numbers PRIMARY KEY CLUSTERED (N)
3. Execute the following statements and check the returned result:
DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
SET @StartDateTime= '2009-10-26 5:00:00'
SET @EndDateTime= '2009-10-26 15:00:00'
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT Halls.HallName, BookStatus.* FROM Halls '
SET @SQL += 'INNER JOIN (SELECT HallID'
SELECT @SQL += ', MAX(CASE WHEN '+CONVERT(VARCHAR,HH)+' BETWEEN DATEPART(HH,StartDateTime) AND DATEPART(HH,EndDateTime) THEN ''Booked'' ELSE ''Available '' END) AS ''H:'+CONVERT(VARCHAR,HH)+''''
FROM
(
SELECT DATEPART(HH,@StartDateTime)+N AS HH
FROM Numbers
WHERE N <= (DATEPART(HH,@EndDateTime)-DATEPART(HH, @StartDateTime))
) AS HoursBetween
SET @SQL += ' FROM HallBooking WHERE DATEDIFF(DD,0,StartDateTime) ='+CONVERT(VARCHAR,DATEDIFF(DD,0,@StartDateTime))+' GROUP BY HallID ) AS BookStatus ON Halls.HallID = BookStatus.HallID'
EXEC(@sql)
Jian Kang
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Marked as answer by anzer on Dec 17, 2009 10:47 AM
Wow Jian.. its EXCELLENT..Thanks.. I was trying lot of different ways for some days and nothintg worked so far.. now I have something to work on..
One main problem with the above code for me is the slots can be booked from any time to any time.. means it can be from 7.10 AM to 8.15 AM and so on.. but each slots will be with in a day only..
Also two more parameters will be there in the search.. one is there will be an Opening and Closing time for each halls and other is Capacity for each hall..
Hall
- HallId
- Name
- HallStartingTime
- HallClosingTime
- Capacity
One main problem with the above code for me is the slots can be booked from any time to any time.. means it can be from 7.10 AM to 8.15 AM and so on.. but each slots will be with in a day only..
My previous sample is not suitable. Let’s go back to your orignal requirement:
anzer
A user can search for hall availability by specifying a start datetime and end datetime.. the query should pick and show whether the hall is available for that time.
Please note: For simplicity, I use two new data types in SQL Server 2008: DATE and TIME. If you do not have SQL Server 2008, we need to do some conversions.
CREATE TABLE Halls
(
Hallid INT IDENTITY ( 1 , 1 ),
Hallname NVARCHAR(50),
HallStartingTime TIME,
HallClosingTime TIME,
Capacity INT
)
CREATE TABLE Hallbooking
(
HallbookingID INT IDENTITY ( 1 , 1 ),
HallID INT,
BookingDate DATE,
BookingStart TIME,
BookingEnd TIME
)
INSERT INTO Halls
VALUES ('Hall1', '10:00 AM', '11:30 PM', 500)
INSERT INTO Halls
VALUES ('Hall2', '2:00 PM', '10:30 PM', 200)
INSERT INTO Halls
VALUES ('Hall3', '7:00 AM', '11:30 PM', 400)
INSERT INTO Halls
VALUES ('Hall4', '1:00 PM', '11:30 PM', 500)
INSERT INTO Hallbooking
VALUES (1,'2009-10-26',
'10:00:00',
'10:30:00')
INSERT INTO Hallbooking
VALUES (2,'2009-10-26',
'16:15:00',
'19:30:00')
INSERT INTO Hallbooking
VALUES (3,'2009-10-26',
'7:00:00',
'14:00:00')
INSERT INTO Hallbooking
VALUES (1,'2009-10-26',
'12:30:00',
'14:00:00')
INSERT INTO Hallbooking
VALUES (2,'2009-10-26',
'19:30:00',
'21:15:00')
INSERT INTO Hallbooking
VALUES (1,'2009-10-26',
'10:00:00',
'11:30:00')
INSERT INTO Hallbooking
VALUES (2,'2009-10-28',
'18:15:00',
'19:00:00')
INSERT INTO Hallbooking
VALUES (1,'2009-10-28',
'14:05:00',
'16:10:00')
INSERT INTO Hallbooking
VALUES (1,'2009-10-28',
'18:30:00',
'20:00:00')
INSERT INTO Hallbooking
VALUES (2,'2009-10-28',
'21:30:00',
'22:15:00')
DECLARE @BookingDate DATE
DECLARE @BookingStart TIME
DECLARE @BookingEnd TIME
DECLARE @Capacity INT
SET @BookingDate = '2009-10-26'
SET @BookingStart = '11:31:00'
SET @BookingEnd = '11:33:00'
SET @Capacity = 200
SELECT * FROM Halls
WHERE HallID NOT IN
(
SELECT DISTINCT HallID FROM HallBooking
WHERE BookingDate = @BookingDate
AND (
(BookingStart BETWEEN @BookingStart AND @BookingEnd)
OR (BookingEnd BETWEEN @BookingStart AND @BookingEnd)
OR (BookingStart <= @BookingStart AND BookingEnd >= @BookingEnd)
)
)
AND Capacity >= @Capacity
AND HallStartingTime <= @BookingStart
AND HallClosingTime >= @BookingEnd
<div style="overflow: hidden; position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px;" id="_mcePaste">
A user can search for hall availability by specifying a start datetime and end datetime.. the query should pick and show whether the hall is available for that time.</div>
Jian Kang
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Marked as answer by anzer on Dec 17, 2009 10:47 AM
I'm trying to build the room booking system using asp.net and VB.net. I found that one of the best suggestions to build the system is yours. But when i tried this I couldn't get the result like the one you have got. Can you please change your statements
like sql query so that it can be used directly in .net sqldatasource.
I'm very interested on this posting that you've made sometimes ago.
However, could you please let me know what does the columns "H:5", "H:6", "H:7", and so on represent?
anzer
Star
7928 Points
1506 Posts
Need help writing an SQL query - date availability search
Oct 21, 2009 10:51 AM|LINK
I need help in writing query to find the time slot availabality for a hall booking system.. details are given below
I have a Hall table which stores the hall details and HallBooking table which stores the start and from time of the bookings done..
Hall
- HallId
- Name
HallBooking
- HallBookingId
- HallId
- BookingPersonName
- StartDateTime
- EndDateTime
A user can search for hall availability by specifying a start datetime and end datetime.. the query should pick and show whether the hall is available for that time..
If the time slot is NOT available the query should pick available slots with same duration for that day and show to the user.
Thanks in advance
Anz
<div style="overflow: hidden; position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px;" id="_mcePaste">DECLARE @StartTime DATETIME</div>
ClientSideAsp.Net | Blog
Jian Kang - ...
All-Star
33132 Points
2465 Posts
Re: Need help writing an SQL query - date availability search
Oct 26, 2009 10:12 AM|LINK
Hi Anz,
Please elaborate a bit? I think we can list the booking statuses to let users choose available slots.
Please refer to the following sample:
1. Create tables and insert sample data
CREATE TABLE Halls ( hallid INT IDENTITY ( 1 , 1 ), hallname NVARCHAR(50)) CREATE TABLE Hallbooking ( hallbookingid INT IDENTITY ( 1 , 1 ), hallid INT, startdatetime DATETIME, enddatetime DATETIME) INSERT INTO Halls VALUES ('Hall1') INSERT INTO Halls VALUES ('Hall2') INSERT INTO Halls VALUES ('Hall3') INSERT INTO Halls VALUES ('Hall4') INSERT INTO Hallbooking VALUES (1, '2009-10-26 7:00:00', '2009-10-26 11:00:00') INSERT INTO Hallbooking VALUES (2, '2009-10-26 9:00:00', '2009-10-26 12:00:00') INSERT INTO Hallbooking VALUES (3, '2009-10-26 13:00:00', '2009-10-26 14:00:00') INSERT INTO Hallbooking VALUES (1, '2009-10-27 5:00:00', '2009-10-26 6:00:00') INSERT INTO Hallbooking VALUES (2, '2009-10-27 6:00:00', '2009-10-26 7:00:00')2. Create a Numbers table
Related link:
How to Create and Use A Numbers Table
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable&referringTitle=Home
3. Execute the following statements and check the returned result:
DECLARE @StartDateTime DATETIME DECLARE @EndDateTime DATETIME SET @StartDateTime= '2009-10-26 5:00:00' SET @EndDateTime= '2009-10-26 15:00:00' DECLARE @SQL VARCHAR(MAX) SET @SQL = 'SELECT Halls.HallName, BookStatus.* FROM Halls ' SET @SQL += 'INNER JOIN (SELECT HallID' SELECT @SQL += ', MAX(CASE WHEN '+CONVERT(VARCHAR,HH)+' BETWEEN DATEPART(HH,StartDateTime) AND DATEPART(HH,EndDateTime) THEN ''Booked'' ELSE ''Available '' END) AS ''H:'+CONVERT(VARCHAR,HH)+'''' FROM ( SELECT DATEPART(HH,@StartDateTime)+N AS HH FROM Numbers WHERE N <= (DATEPART(HH,@EndDateTime)-DATEPART(HH, @StartDateTime)) ) AS HoursBetween SET @SQL += ' FROM HallBooking WHERE DATEDIFF(DD,0,StartDateTime) ='+CONVERT(VARCHAR,DATEDIFF(DD,0,@StartDateTime))+' GROUP BY HallID ) AS BookStatus ON Halls.HallID = BookStatus.HallID' EXEC(@sql)Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Jian Kang - ...
All-Star
33132 Points
2465 Posts
Re: Need help writing an SQL query - date availability search
Oct 26, 2009 10:17 AM|LINK
Here is the result screeshot:
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
anzer
Star
7928 Points
1506 Posts
Re: Need help writing an SQL query - date availability search
Oct 27, 2009 05:39 AM|LINK
Wow Jian.. its EXCELLENT..Thanks.. I was trying lot of different ways for some days and nothintg worked so far.. now I have something to work on..
One main problem with the above code for me is the slots can be booked from any time to any time.. means it can be from 7.10 AM to 8.15 AM and so on.. but each slots will be with in a day only..
Also two more parameters will be there in the search.. one is there will be an Opening and Closing time for each halls and other is Capacity for each hall..
Hall
- HallId
- Name
- HallStartingTime
- HallClosingTime
- Capacity
HallBooking
- HallBookingId
- HallId
- BookingPersonName
- StartDateTime
- EndDateTime
The users who search the hall will enter the maximum capcity they needed..
ClientSideAsp.Net | Blog
anzer
Star
7928 Points
1506 Posts
Re: Need help writing an SQL query - date availability search
Oct 27, 2009 05:41 AM|LINK
CREATE TABLE Halls ( hallid INT IDENTITY ( 1 , 1 ), hallname NVARCHAR(50), HallStartingTime VARCHAR(20), -- if its a bad idea to store as VARCHAR, we can change this to Minutes in INT.. so 11 AM will be 660 and 2 PM will be 840 HallClosingTime VARCHAR(20), -- same comment for the above column Capacity INT ) CREATE TABLE Hallbooking ( hallbookingid INT IDENTITY ( 1 , 1 ), hallid INT, startdatetime DATETIME, enddatetime DATETIME ) INSERT INTO Halls VALUES ('Hall1', '10:00 AM', '11:30 PM', 500) INSERT INTO Halls VALUES ('Hall2', '2:00 PM', '10:30 PM', 200) INSERT INTO Halls VALUES ('Hall3', '7:00 AM', '11:30 PM', 400) INSERT INTO Halls VALUES ('Hall4', '1:00 PM', '11:30 PM', 500) --bookings for 10/26 INSERT INTO Hallbooking VALUES (1, '2009-10-26 11:00:00', '2009-10-26 11:30:00') INSERT INTO Hallbooking VALUES (2, '2009-10-26 16:15:00', '2009-10-26 19:30:00') INSERT INTO Hallbooking VALUES (3, '2009-10-26 7:00:00', '2009-10-26 14:00:00') INSERT INTO Hallbooking VALUES (1, '2009-10-26 12:30:00', '2009-10-26 14:00:00') INSERT INTO Hallbooking VALUES (2, '2009-10-26 19:30:00', '2009-10-26 21:15:00') --bookings for 10/28 INSERT INTO Hallbooking VALUES (1, '2009-10-28 10:00:00', '2009-10-28 11:30:00') INSERT INTO Hallbooking VALUES (2, '2009-10-28 18:15:00', '2009-10-28 19:00:00') INSERT INTO Hallbooking VALUES (1, '2009-10-28 14:05:00', '2009-10-28 16:10:00') INSERT INTO Hallbooking VALUES (1, '2009-10-28 18:30:00', '2009-10-28 20:00:00') INSERT INTO Hallbooking VALUES (2, '2009-10-28 21:30:00', '2009-10-28 22:15:00')ClientSideAsp.Net | Blog
Jian Kang - ...
All-Star
33132 Points
2465 Posts
Re: Need help writing an SQL query - date availability search
Oct 28, 2009 08:44 AM|LINK
My previous sample is not suitable. Let’s go back to your orignal requirement:
Please note: For simplicity, I use two new data types in SQL Server 2008: DATE and TIME. If you do not have SQL Server 2008, we need to do some conversions.
CREATE TABLE Halls ( Hallid INT IDENTITY ( 1 , 1 ), Hallname NVARCHAR(50), HallStartingTime TIME, HallClosingTime TIME, Capacity INT ) CREATE TABLE Hallbooking ( HallbookingID INT IDENTITY ( 1 , 1 ), HallID INT, BookingDate DATE, BookingStart TIME, BookingEnd TIME ) INSERT INTO Halls VALUES ('Hall1', '10:00 AM', '11:30 PM', 500) INSERT INTO Halls VALUES ('Hall2', '2:00 PM', '10:30 PM', 200) INSERT INTO Halls VALUES ('Hall3', '7:00 AM', '11:30 PM', 400) INSERT INTO Halls VALUES ('Hall4', '1:00 PM', '11:30 PM', 500) INSERT INTO Hallbooking VALUES (1,'2009-10-26', '10:00:00', '10:30:00') INSERT INTO Hallbooking VALUES (2,'2009-10-26', '16:15:00', '19:30:00') INSERT INTO Hallbooking VALUES (3,'2009-10-26', '7:00:00', '14:00:00') INSERT INTO Hallbooking VALUES (1,'2009-10-26', '12:30:00', '14:00:00') INSERT INTO Hallbooking VALUES (2,'2009-10-26', '19:30:00', '21:15:00') INSERT INTO Hallbooking VALUES (1,'2009-10-26', '10:00:00', '11:30:00') INSERT INTO Hallbooking VALUES (2,'2009-10-28', '18:15:00', '19:00:00') INSERT INTO Hallbooking VALUES (1,'2009-10-28', '14:05:00', '16:10:00') INSERT INTO Hallbooking VALUES (1,'2009-10-28', '18:30:00', '20:00:00') INSERT INTO Hallbooking VALUES (2,'2009-10-28', '21:30:00', '22:15:00')<div style="overflow: hidden; position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px;" id="_mcePaste"> A user can search for hall availability by specifying a start datetime and end datetime.. the query should pick and show whether the hall is available for that time.</div>
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
sbhavan_s
Member
13 Points
15 Posts
Re: Need help writing an SQL query - date availability search
Nov 24, 2009 02:49 AM|LINK
Hi Jian-kang,
I'm trying to build the room booking system using asp.net and VB.net. I found that one of the best suggestions to build the system is yours. But when i tried this I couldn't get the result like the one you have got. Can you please change your statements like sql query so that it can be used directly in .net sqldatasource.
Thanks in advance.
sarahcheong
Member
2 Points
1 Post
Re: Need help writing an SQL query - date availability search
Jul 14, 2010 10:49 AM|LINK
Hi Jian Kang,
I'm very interested on this posting that you've made sometimes ago.
However, could you please let me know what does the columns "H:5", "H:6", "H:7", and so on represent?
Appreciate your reply.
Regards,
Sarah Cheong