CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
AJMi
AJMi

Reputation: 33

How to check time all time slots in sql query

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

Upvotes: 0

Views: 98

Answers (1)

Sander
Sander

Reputation: 4042

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)

Upvotes: 1

Related Questions