sql – How to check if there is a schedule in a time range?

Question:

I need to create a schedule where I allocate a service provider to work for a client.

For example: PrestadorDeServicos João will provide services to the customer condominium vale do sol on March 15, 2016 from 10:00 am to 12:00 pm .

João cannot be registered on the same day/time for another client.

Having the PrestadorDeServicos table with name and PK cod_prestador , Customer with razaoSocial and PK cod_cliente and Scheduling with Start DateTime, End DateTime, FK_prestador, FK_Cliente and PK cod_agendamento .

How do I ensure that a service provider is never allocated at the same time to different clients?

Answer:

You can create a check before doing the Insert , checking if your table already contains the information you are passing.

declare @table table
(
  cod_prestador int,
  DateTimeInicio DateTime , 
  DateTimeFim DateTime
)

declare @DateTimeInicio datetime = '15-03-2016 12:00:00', @DateTimeFim DateTime = '15-03-2016 14:00:00'

insert into @table values 
(1,'15-03-2016 10:00:00', '15-03-2016 12:00:00')

IF EXISTS (SELECT 1 FROM @table 
            WHERE @DateTimeFim <= DateTimeFim 
            AND @DateTimeInicio >= DateTimeInicio)

  print 'Aqui existi, não faça o insert ou retorne o erro.'
ELSE
   print 'Aqui você pode inserir'
Post Views: 4
Scroll to Top