We help IT Professionals succeed at work.

Room booking SQL help

176 Views
Last Modified: 2014-11-24
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

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Can you post some sample data so I can check what's wrong?

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ãoIT Engineer
CERTIFIED EXPERT
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
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Great thank you

Andy
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.