SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help in writing Query for hall booking date availability search


Help in writing Query for hall booking date availability search

Author
Message
Anzer Muhammad
Anzer Muhammad
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 11
I need help in writing query to find the time slot availability 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
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9431 Visits: 25280
Anzer Muhammad
An interesting and challenging question.

However it is more likely to be answered if you follow the examples of how to post a question (refer to the link in my signature block). Please expand you question to include table definitions (Create Table ...) as well as some sample data for those tables. Also include what you expect the output to be.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
ALZDBA
ALZDBA
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15687 Visits: 8967
So you should start with a "timeslot" table.

Did you read these wonderful articles ?
- The "Numbers" or "Tally" Table: What it is and how it replaces a loop. By Jeff Moden http://www.sqlservercentral.com/articles/T-SQL/62867/

Look for "How about making a "shift" table "

- Generating Missing Dates and Numbers By Jacob Sebastian http://www.sqlservercentral.com/articles/Datetime+Manipulation/61822/

- Useful Dates: The Many Uses of Date Tables By Brandon Galderisi http://www.sqlservercentral.com/articles/Date+Manipulation/65195/

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Anzer Muhammad
Anzer Muhammad
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 11
The time slot is flexible. Users can choose the start and end from any time to any time, but with in a day. So I think I can not have an available slot table..
Anzer Muhammad
Anzer Muhammad
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 11
After spending lot of time on this I got that its very very difficult to solve this problem unless I have an available slots table.. but as I posted earlier, the slots are flexible and can be choosen from any time to any time with in a day.. so managing all the available slots in a predefined table is not possible..

A solution I was thinking is to write a view from the current booking table which will show all the slots available from the current bookings.. Can anyone please help me on this

The table structures, sample data and expected results are below



CREATE TABLE Halls
(
hallId INT IDENTITY ( 1 , 1 ),
hallName NVARCHAR(50),
hallStartingTime INT, -- 11 AM will be stored as 660 and 2 PM will be stored as 840
hallClosingTime INT, -- same comment for the above column
capacity INT
)

CREATE TABLE Hallbooking
(
hallBookingId INT IDENTITY ( 1 , 1 ),
hallId INT,
startDatetime DATETIME,
endDatetime DATETIME
)

-- halls

INSERT INTO Halls VALUES ('Hall 1', 540, 1320, 500) --available from 9 AM till 10 PM

INSERT INTO Halls VALUES ('Hall 2', 840, 1350, 200) --available from 2 PM till 10:30 PM

INSERT INTO Halls VALUES ('Hall3', 450, 1410, 400) --available from 7:30 AM till 11:30 PM

--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 (1, '2009-10-26 12:30:00', '2009-10-26 14:00:00')

INSERT INTO Hallbooking VALUES (1, '2009-10-26 16:00:00', '2009-10-26 18: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')



-- SAMPLE OUT PUT DATA

hallId startDatetime endDatetime
----------- ----------------------- -----------------------
1 2009-10-26 9:00:00.000 2009-10-26 11:00:00.000 --from opening time till first booking
1 2009-10-26 11:30:00.000 2009-10-26 12:30:00.000 --from first bookings end time till next bookings starting time
1 2009-10-26 14:00:00.000 2009-10-26 16:00:00.000
1 2009-10-26 18:00:00.000 2009-10-26 22:00:00.000 -- from last booking time till Hall closing time

1 2009-10-28 9:00:00.000 2009-10-28 10:00:00.000 --from opening time till first booking
1 2009-10-28 11:30:00.000 2009-10-28 14:05:00.000
1 2009-10-28 16:10:00.000 2009-10-28 18:30:00.000
1 2009-10-28 20:00:00.000 2009-10-28 22:00:00.000 -- from last booking time for the Day till Hall closing time

2 2009-10-26 14:00:00.000 2009-10-26 16:15:00.000 -- from opening time till first booking
2 2009-10-26 21:15:00.000 2009-10-26 22:30:00.000 -- from last booking time for the Day till Hall closing time

2 2009-10-28 14:00:00.000 2009-10-28 18:15:00.000 -- from opening time till first booking
2 2009-10-28 19:00:00.000 2009-10-28 21:30:00.000
2 2009-10-28 22:15:00.000 2009-10-28 22:30:00.000 -- from last booking time for the Day till Hall closing time



Sergiy
Sergiy
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13687 Visits: 12132
-
Sergiy
Sergiy
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13687 Visits: 12132
Sorry, no time for putting proper comments in there, I hope you can figure it out yourself.

Column N_Date in table Tally contains all sequential dates from '1900-01-01' to '2076-12-31'.


select H.hallId, T.N_Date, DATEADD(n, H.hallStartingTime, T.N_Date) Avail_From, ISNULL(MIN(B.startDatetime), DATEADD(n, H.hallClosingTime, T.N_Date) ) AS Avail_To
-- from beginning of the day to the next appointment
from Halls H
INNER JOIN dbo.Tally T ON T.N_Date >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE())/7*7, 0) AND T.N_Date < DATEADD(dd, (DATEDIFF(dd, 0, GETDATE())/7+1)*7, 0)
left join Hallbooking B ON H.hallId = B.hallId AND B.startDatetime > T.N_Date AND B.startDatetime < T.N_Date+1
AND B.startDatetime > DATEADD(n, H.hallStartingTime, T.N_Date)
GROUP BY H.hallId, H.hallClosingTime, H.hallStartingTime, T.N_Date
UNION
select H.hallId, T.N_Date, ISNULL(MAX(B.endDatetime), DATEADD(n, H.hallStartingTime, T.N_Date)) AS Avail_From, DATEADD(n, H.hallClosingTime, T.N_Date) Avail_To
-- from the last appointment to end of the day
from Halls H
INNER JOIN dbo.Tally T ON T.N_Date >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE())/7*7, 0) AND T.N_Date < DATEADD(dd, (DATEDIFF(dd, 0, GETDATE())/7+1)*7, 0)
left join Hallbooking B ON H.hallId = B.hallId AND B.endDatetime > T.N_Date AND B.endDatetime < T.N_Date+1
AND B.endDatetime < DATEADD(n, H.hallClosingTime, T.N_Date)
GROUP BY H.hallId, H.hallStartingTime, H.hallClosingTime, T.N_Date
UNION
select B1.hallId, T.N_Date, B1.endDatetime AS Avail_From, MIN(B2.startDatetime) Avail_To
-- slots between appointments
from dbo.Tally T
INNER join Hallbooking B1 ON B1.endDatetime > T.N_Date AND B1.endDatetime < T.N_Date+1
INNER JOIN Hallbooking B2 ON B2.hallId = B1.hallId AND B2.startDatetime > B1.endDatetime AND B2.endDatetime < T.N_Date+1
WHERE T.N_Date >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE())/7*7, 0) AND T.N_Date < DATEADD(dd, (DATEDIFF(dd, 0, GETDATE())/7+1)*7, 0)
GROUP BY B1.hallId, B1.endDatetime, T.N_Date
ORDER BY H.hallId, Avail_From, Avail_To



I believe this returns availability slots you were looking for.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search