MS SQL Server Query to Check if Hall is Booked-CodePudding

This query is driving me crazy, any help is appreciated.

I have a table as follows:


CREATE TABLE Bookings
(
    Id bigInt IDENTITY(1,1),
    hallId bigInt,
    startTime smallDateTime,
    endTime smallDateTime
)

INSERT INTO Bookings 
VALUES (1, '2022-10-03 08:00:00', '2022-10-03 10:00:00') 

--Edit( One of my trials so far

DECLARE @startTime AS SmallDateTime = '2022-10-03 08:00:00'
DECLARE @endTime AS SmallDateTime = '2022-10-03 10:0:00'    
DECLARE @hallId AS bigInt = 1

SELECT * FROM Bookings WHERE 
            startTime >= FORMAT(@startTime, 'yyyy-dd-MM HH:mm:ss') OR startTime <= FORMAT(@endTime, 'yyyy-dd-MM HH:mm:ss') AND
            endTime >=  FORMAT(@startTime, 'yyyy-dd-MM HH:mm:ss') OR endtime <= FORMAT(@endTime, 'yyyy-dd-MM HH:mm:ss') 
AND hallId=@hallId

-- Also this Way 

DECLARE @startTime AS SmallDateTime = '2022-03-10 08:00:00'
DECLARE @endTime AS SmallDateTime = '2022-03-10 10:0:00'    
DECLARE @hallId AS bigInt = 1

SELECT * FROM Bookings WHERE 
      startTime >= @startTime OR startTime <= @endTime
          AND
      endTime >= @startTime OR endtime <= @endTime 
      AND hallId=@hallId

--Edit)

My Datetime format is 'yyyy-MM-dd HH:mm:ss' but it's records are entered in yyyy-dd-MM HH:mm:ss format

So what I want is a query that checks for 2 datetime ranges (say 2022-10-03 08:00:00 - 2022-10-03 10:00:00), if there is a match it returns the data otherwise it returns nothing.

My goal is to check if either of the startTime OR endTime falls within an existing Booking (e.g None of startTime or endTime can't be within '2022-10-03 08:00:00' up to '2022-10-03 10:00:00'), if found, the booking can't precede unless he/she has to alter. I tried between, but seems to work one way and fails another

It would be a plus if someone could check in separately that is to display a specific message/status for startTime and endTime (I mean if the startTime corresponds to existing Booking msg='Select a different start time' and if The endTime corresponds to existing Booking msg='Select a different end time'

Also I tried to check if the difference of startTime and endTime is less then 1 hour.

BWT, I'm using SQL Server 2014 Express

CodePudding user response：

You can do INSERT...SELECT...WHERE NOT EXISTS, then check the rowcount afterwards. To compare for interval overlap, compare one start time with the other end, and the reverse.

Note that you should always specify column names for inserts, and pass values as parameters, not text.

INSERT INTO Bookings (hallId, startTime, endTime)
SELECT @hallId, @startTime, @endTime
WHERE NOT EXISTS (SELECT 1
    FROM Bookings b WITH (UPDLOCK)  -- needs UPDLOCK to prevent race conditions
    WHERE b.hallId = @hallId
      AND b.startTime < @endTime
      AND b.endTime > @startTime
);

IF @@ROWCOUNT = 0
    THROW 50001, N'Overlapping booking exists', 1;

You may want to change to <= if you don't want to allow two intervals to abut.

CodePudding user response：

Thanks to who ever tried to help, I figured it out,

Sometimes all you need is to take a quick break.

I found out that smallDateTime data type was acting crazy, it was swapping the month and the day while inserting in the table

e.g if I Inserted 2022-03-15 20:00:00 it would be recorded as 2022-15-03 20:00:00 and vice-versa

So Here goes what worked for me, It may help someone in the nearer or far future

-- 1, 2022-03-11 13:30:00, 2022-03-11 15:00:00 => sample data 1 in the table 

-- 1, 2022-03-11 09:30:00, 2022-03-11 12:30:00 => sample data 2 in the table



DECLARE @startTime AS SmallDateTime = '2022-03-11 15:01:00'
DECLARE @endTime AS SmallDateTime =   '2022-03-11 19:30:00' 
DECLARE @hallId AS bigInt = 1


SELECT * FROM Bookings WHERE 
(startTime BETWEEN @startTime AND @endTime) OR 
(endTime BETWEEN @startTime AND @endTime) AND hallId=@hallId