<div>
all booked rooms between 2 dates 

<pre><code class="code-1 nolinks" id="code-20-40461788-1">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 &gt;= @Date1 AND S.End &lt;= @Date1
	AND S.Start &gt;= @Date2 AND S.End &lt;= @Date2
</code></pre>
 
all rooms with free slots between 2 dates 

<pre><code class="code-1 nolinks" id="code-20-40461788-2">SELECT R.RoomName
FROM Rooms R
WHERE NOT EXISTS (SELECT 1 
		FROM RoomsSchedule S 
		WHERE S.RoomID = R.RoomID
			AND S.Start &gt;= @Date1 AND S.End &lt;= @Date1
			AND S.Start &gt;= @Date2 AND S.End &lt;= @Date2)
</code></pre>
</div>

<div>
Thanks Vitor 
 
Are the &gt;= &amp;&nbsp;&lt;= correct I'm not getting any records. I can't see where startdate will ever be greater than the end date. 
 
Andy
</div>

<div>
Can you post some sample data so I can check what's wrong?
</div>

<div>
<pre><code class="code-10 nolinks" id="code-20-40461898-1">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 &gt;= @Date1 AND S.SlotEnd &lt;= @Date1
	AND S.SlotStart &gt;= @Date2 AND S.SlotEnd &lt;= @Date2
</code></pre>
</div>

<div>
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.
</div>

<div>
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 &nbsp;00:00:00' 
declare @Date2 smalldatetime 
set @Date2='2014-11-15 00:00:00' 
 
Andy
</div>

<div>
Great thank you 
 
Andy
</div>

<div>
<div class="content wysiwyg-content">
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
</div>
</div>

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

Room booking SQL help

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

The successor to Active Server Pages, ASP.NET websites utilize the .NET framework to produce dynamic, data and content-driven web applications and services. ASP.NET code can be written using any .NET supported language. As of 2009, ASP.NET can also apply the Model-View-Controller (MVC) pattern to web applications