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'