Solved

Room booking SQL help

Posted on 2014-11-24
8
145 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
0
Comment
Question by:Andy Green
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40461788
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

0
 
LVL 3

Author Comment

by:Andy Green
ID: 40461844
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
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40461847
Can you post some sample data so I can check what's wrong?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 3

Author Comment

by:Andy Green
ID: 40461898
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

0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40461905
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.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40461930
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
0
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40462135
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

0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 40462184
Great thank you

Andy
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question