October 21, 2009 at 6:21 am
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
October 21, 2009 at 6:56 am
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.
October 21, 2009 at 7:01 am
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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 21, 2009 at 7:20 am
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..
October 27, 2009 at 3:08 am
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
(
hallIdINT IDENTITY ( 1 , 1 ),
hallNameNVARCHAR(50),
hallStartingTimeINT, -- 11 AM will be stored as 660 and 2 PM will be stored as 840
hallClosingTimeINT, -- same comment for the above column
capacityINT
)
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
October 27, 2009 at 9:57 pm
-
_____________
Code for TallyGenerator
October 27, 2009 at 10:02 pm
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.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply