Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help in writing Query for hall booking date availability search Expand / Collapse
Author
Message
Posted Wednesday, October 21, 2009 6:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 11, 2010 12:58 AM
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
Post #806411
Posted Wednesday, October 21, 2009 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #806450
Posted Wednesday, October 21, 2009 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:42 AM
Points: 6,743, Visits: 8,515
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #806454
Posted Wednesday, October 21, 2009 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 11, 2010 12:58 AM
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..
Post #806473
Posted Tuesday, October 27, 2009 3:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 11, 2010 12:58 AM
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


Post #809146
Posted Tuesday, October 27, 2009 9:57 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
-
Post #809704
Posted Tuesday, October 27, 2009 10:02 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
Post #809706
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse