Room booking SQL help

Andy Green
Andy Green used Ask the Experts™
on
Hi

I'm writing a room booking system to integrate with an existing system. I'm sure the SQL I need has been written before can anyone help.

I have a few tables the key ones are Rooms

RoomID
RoomName

and RoomsSchedule

RoomScheduleID
RoomID
Decsription
Start
End

I need the sql to select all booked rooms between 2 dates, and all rooms with free slots between 2 dates.

This will be further complicated as I need recurring bookings, but don't know how I'm going to do that yet.

Andy
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
all booked rooms between 2 dates
SELECT S.RoomScheduleID, R.RoomName, S.Decsription, S.Start, S.End
FROM Rooms R
	INNER JOIN RoomsSchedule S ON (S.RoomID = R.RoomID)
WHERE S.Start >= @Date1 AND S.End <= @Date1
	AND S.Start >= @Date2 AND S.End <= @Date2

Open in new window


all rooms with free slots between 2 dates
SELECT R.RoomName
FROM Rooms R
WHERE NOT EXISTS (SELECT 1 
		FROM RoomsSchedule S 
		WHERE S.RoomID = R.RoomID
			AND S.Start >= @Date1 AND S.End <= @Date1
			AND S.Start >= @Date2 AND S.End <= @Date2)

Open in new window

Author

Commented:
Thanks Vitor

Are the >= & <= correct I'm not getting any records. I can't see where startdate will ever be greater than the end date.

Andy
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
Can you post some sample data so I can check what's wrong?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Declare  @Rooms TABLE
( 
Room_ID int,
RoomName varchar(20)
)
insert into @Rooms values (1, 'Room 1'), (2, 'Room 2'),(3, 'Room 3')


Declare  @RoomsSchedule TABLE
( 
RoomsSchedule_ID int,
Room_ID int,
Decsription varchar(20),
SlotStart smalldatetime,
SlotEnd smalldatetime
)

insert into @RoomsSchedule values 
(1, 1, 'Room 1 booking', '2014-11-04 00:10:00', '2014-11-04 00:12:00'),
(2, 1, 'Room 1 booking', '2014-11-10 00:09:00', '2014-11-10 00:14:00'),
(3, 1, 'Room 1 booking', '2014-11-15 00:14:00', '2014-11-15 00:18:00'),
(1, 2, 'Room 2 booking', '2014-12-04 00:10:00', '2014-12-04 00:12:00'),
(2, 2, 'Room 2 booking', '2014-12-10 00:09:00', '2014-12-10 00:14:00'),
(3, 2, 'Room 2 booking', '2014-12-15 00:14:00', '2014-12-15 00:18:00')


declare @Date1 smalldatetime
set @Date1='2014-11-18 00:00:00'
declare @Date2 smalldatetime
set @Date2='2014-11-20 00:00:00'
			

SELECT S.RoomsSchedule_ID, R.RoomName, S.Decsription, S.SlotStart, S.SlotEnd
FROM @Rooms R
	INNER JOIN @RoomsSchedule S ON (S.Room_ID = R.Room_ID)
WHERE S.SlotStart >= @Date1 AND S.SlotEnd <= @Date1
	AND S.SlotStart >= @Date2 AND S.SlotEnd <= @Date2

Open in new window

Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
Well, you really don't have any rows to return because there's no rooms occupied between '2014-11-18 00:00:00'
and '2014-11-20 00:00:00'
. But if you run my 2nd query you'll have the free rooms.

Author

Commented:
Using the parameters below should it return this appointment - (2, 1, 'Room 1 booking', '2014-11-10 00:09:00', '2014-11-10 00:14:00')

declare @Date1 smalldatetime
set @Date1='2014-11-04  00:00:00'
declare @Date2 smalldatetime
set @Date2='2014-11-15 00:00:00'

Andy
MSSQL Senior Engineer
Distinguished Expert 2017
Commented:
Ok, I understood.
Try these SELECT's then:
SELECT S.RoomsSchedule_ID, R.RoomName, S.Decsription, S.SlotStart, S.SlotEnd
FROM @Rooms R
	INNER JOIN @RoomsSchedule S ON (S.Room_ID = R.Room_ID)
WHERE S.SlotStart >= @Date1 AND S.SlotEnd <= @Date2

Open in new window

SELECT R.RoomName
FROM Rooms R
WHERE NOT EXISTS (SELECT 1 
		FROM RoomsSchedule S 
		WHERE S.RoomID = R.RoomID
			AND S.Start >= @Date1 AND S.End <= @Date2)

Open in new window

Author

Commented:
Great thank you

Andy

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial