Logic for Getting Non-Colliding Rooms in SQL Database

This document explains the logic for getting rooms which are not collided based on startdatetime and enddatetime in a SQL database. It provides step-by-step instructions for creating the necessary tables, inserting test data, and creating a stored procedure to fetch the list of free rooms without conflicts. The procedure takes input parameters for startdatetime and enddatetime and returns the available rooms. This solution is useful for managing hotel bookings or any scenario where room availability needs to be checked.

Introduction
This logic of getting rooms which are not collided based on startdatetime and endate time is developed
using Microsoft SQL database as suggested.
Explanation
Create the table with the name Room Table with two Columns RoomId and Active.
RoomId is a Primary key value and the value will be unique, that means same value will not repeat again.
Active is we will be checking is this room is under repairs or any other works we will make as inactive or
it will be in active state. Currently this logic is not impletemented, if we want you can add this in the
where condition
Table will be created using following steps
1. Create DataBase with name Hotels and expand the database.
2. Click on Tables and Right click the table and create table.
3. Give the column names and data type in the table and save it with the name tblRooms
4. Same procedure for creating another table tblBooking.
Now we will define the about the second table structure and this table with name tblBooking having
columns Bookingid which is unique value and autoincrement id with datatype int, StartDateTime is with
datatype datetime which is the end of the booking for that particular room, EndDateTime is with
datatype datetime which is the end of the booking of that particulat room, Roomid defined for which
room it is been defined
This two table initially loaded with default data as shown below by using INSERT command.
If you want to insert more values we can use same command by changing values
To Set PrimarKey, Select the column which u required and right click and set as Primary key value
The document is having tables , Test data inserted and stored procedures
Table Struct and Data as below:
USE [hotels]
GO
/****** Object: Table [dbo].[tblBooking] Script Date: 16-06-2019 10:04:21
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblBooking](
[Bookingid] [int] IDENTITY(1,1) NOT NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
[Roomid] [int] NULL,
CONSTRAINT [PK_tblBooking] PRIMARY KEY CLUSTERED
(

[Bookingid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblRooms] Script Date: 16-06-2019 10:04:21
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblRooms](
[RoomId] [int] NOT NULL,
[Active] [bit] NULL,
CONSTRAINT [PK_tblRooms] PRIMARY KEY CLUSTERED
(
[RoomId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tblBooking] ON
INSERT [dbo].[tblBooking] ([Bookingid], [StartDateTime], [EndDateTime], [Roomid])
VALUES (1, CAST(N'2019-06-17T04:00:00.000' AS DateTime), CAST(N'2019-06-
17T23:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblBooking] ([Bookingid], [StartDateTime], [EndDateTime], [Roomid])
VALUES (3, CAST(N'2019-06-17T04:00:00.000' AS DateTime), CAST(N'2019-06-
17T00:00:00.000' AS DateTime), 2)
INSERT [dbo].[tblBooking] ([Bookingid], [StartDateTime], [EndDateTime], [Roomid])
VALUES (4, CAST(N'2019-06-17T04:00:00.000' AS DateTime), CAST(N'2019-06-
17T00:00:00.000' AS DateTime), 3)
INSERT [dbo].[tblBooking] ([Bookingid], [StartDateTime], [EndDateTime], [Roomid])
VALUES (5, CAST(N'2019-06-17T04:00:00.000' AS DateTime), CAST(N'2019-06-
17T00:00:00.000' AS DateTime), 4)
INSERT [dbo].[tblBooking] ([Bookingid], [StartDateTime], [EndDateTime], [Roomid])
VALUES (6, CAST(N'2019-06-18T22:00:00.000' AS DateTime), CAST(N'2019-06-
19T00:00:00.000' AS DateTime), 4)
SET IDENTITY_INSERT [dbo].[tblBooking] OFF
INSERT [dbo].[tblRooms] ([RoomId], [Active]) VALUES (1, 1)
INSERT [dbo].[tblRooms] ([RoomId], [Active]) VALUES (2, 1)
INSERT [dbo].[tblRooms] ([RoomId], [Active]) VALUES (3, 1)
INSERT [dbo].[tblRooms] ([RoomId], [Active]) VALUES (4, 1)
INSERT [dbo].[tblRooms] ([RoomId], [Active]) VALUES (5, 1)
The Procedure is written based on the output requested for fetching Rooms which is not colliding means
in the return list there will be no conflicts exists,
Here we are passing input parameters as StartDateTime and EndDateTime with type VARCHAR of size
25.
Create temporary table to insert room id which is already book by taking 30 min gap before and after
the startdatetime and enddatetime. StartDateTime will indicated booking start date with time and
EndDateTime indicates booking end date time of the customer.

Temporary table which is created is local temporary variable and after using this temporary variable we
have to drop the table or else it will be created locaaly and cannot run code again u will get table already
exists issue
The temporary table will contains rooms which has already book and list of room id. Inner join of
booking with roomID as out put by using where condition to check the datetime of the bookings exists
or not. Varchar and existing datetime column we will be casting up to get proper out put value DATEADD
function will be used to subtract 30 minutes in enddatetime and subtract 30 minutes in startdatetime.
Inner join indicates commonly exists value will be taken and not in indicated if that roomid doesnot
exists in temptable then that roomid will return in list.
If some more columns is required in the output we can add extra what column required here.
Stored procedure benefits is we can use functions inside stored procedures and stored procedure will be
used to insert update and delete functionality. Also we can call view here to execute result set fast with
required fields. Just go through below code and execute in your local for better result with the given test
data
Stored Procedure tested with test data
--exec GetEmptyRooms '2019-06-18 20:00','2019-06-18 21:00'
CREATE PROCEDURE GetEmptyRooms
(
@StartDateTime VARCHAR(25),
@EndDateTime VARCHAR(25)
)
AS
BEGIN
CREATE TABLE #tmpRooms(RoomId INT)
INSERT INTO #tmpRooms
SELECT A.RoomId FROM tblRooms A
INNER JOIN tblBooking B ON A.RoomId = B.Roomid
WHERE (DATEADD(MINUTE,-30,CAST(B.StartDateTime AS DATETIME)) BETWEEN
CAST(@StartDateTime AS DATETIME) AND CAST(@EndDateTime AS DATETIME))
OR (DATEADD(MINUTE,30,CAST(B.EndDateTime AS DATETIME)) BETWEEN
CAST(@StartDateTime AS DATETIME) AND CAST(@EndDateTime AS DATETIME))
SELECT * FROM tblRooms WHERE RoomId NOT IN (SELECT RoomId FROM #tmpRooms)
DROP TABLE #tmpRooms
END
Select * from tblRooms, Test data inserted as below screen shot

Select * from tblBooking, and already booked details will be having entries in
this table and below is the output

Passing test values to get the list of out put values and query to execute to
get ouput list of rooms as below
exec GetEmptyRooms '2019-06-18 20:00','2019-06-18 21:00'

exec GetEmptyRooms '2019-06-18 20:00','2019-06-18 21:30'

Conclusion

The conclusion here is sql database with stored procedure hotel with rooms and
booking conditons with out colliding will be getting the list of free rooms for
new booking. Continue with this procedure to make entries for new bookings