Solved

Room booking SQL help

Posted on 2014-11-24
8
144 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
  • 4
  • 4
8 Comments
 
LVL 48

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 48

Expert Comment

by:Vitor Montalvão
ID: 40461847
Can you post some sample data so I can check what's wrong?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 48

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 48

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS On fail action 5 37
SQL query and VBA 5 45
Index and Stats Management-Specific tables 8 21
job schedule 8 16
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

807 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