How to check time all time slots in sql query

Question

I have to write query to update roomid comparing rooms based on time slots

I have this table data

customerid    appointmentfrom            appointmentto               roomid
----------------------------------------------------------------------------   
    1         2020-07-18 10:00:00.000    2020-07-18 11:30:00.000        1
    2         2020-07-18 10:30:00.000    2020-07-18 11:15:00.000        2
    3         2020-07-18 11:15:00.000    2020-07-18 11:59:00.000        2

I shouldn't allow customerid 1 to update his roomid as 2 as roomid 2 has been booked for that time slots

customerid 1 is trying to update roomid as 2 , but i need to check whether the appointmentfrom and appointmentto he is booking is available or not

Are you using SQL Server 2008 or 2012 as well? 2008 is completely unsupported, so you should really be looking at upgrade paths by now if you are using it. — Thom A, Jul 17 '20 at 12:09
@AJMi, [this question](https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) may help. — Dan Guzman, Jul 17 '20 at 12:13
This one won't help because I have to compare whether appointment has been booked under that period of customer 1 who is trying to update(10am to 11.30am) his roomid as 2, if booked I shouldn't allow the update — AJMi, Jul 17 '20 at 12:19

Sander · Accepted Answer · 2020-07-17T12:37:47.817

Your question does not state how you get your input or how you want to handle forbidden updates (throw an error?). This solution has parameters as input and does nothing when the update is not allowed. I also included support for when a customer would have multiple appointments.

The where clause uses a (not) exists to only select updatable records.

-- create example
declare @data table
(
    customerid int,
    appointmentfrom datetime,
    appointmentto datetime,
    roomid int
);

insert into @data (customerid, appointmentfrom, appointmentto, roomid) values
(1, '2020-07-18 10:00:00.000', '2020-07-18 11:30:00.000', 1),
(2, '2020-07-18 10:30:00.000', '2020-07-18 11:15:00.000', 2),
(3, '2020-07-18 11:15:00.000', '2020-07-18 11:59:00.000', 2);


-- solution (with parameters)
declare @customerid int = 1;                                    -- specify customer
declare @appointmentfrom datetime = '2020-07-18 10:00:00.000';  -- specify customer appointment
declare @newroomid int = 2;                                     -- specify target room

update d
set d.roomid = @newroomid
from @data d
where d.customerid = @customerid            -- select customer...
  and d.appointmentfrom = @appointmentfrom  -- ... and his appointment
  -- look for any unwanted overlapping meetings on the target room
  and not exists (  select top 1 'x'
                    from @data d2
                    where d2.roomid = @newroomid
                      and d2.appointmentto > d.appointmentfrom
                      and d2.appointmentfrom < d.appointmentto );
-- (0 rows affected)

How to check time all time slots in sql query

1 Answers1