How do I check for scheduling at a time interval?

3

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

  

Eg: The Service Provider John will provide services to the customer    condo valley of the sun on the day March 15, 2016 from 10:00 am to 12:00 .

John can not be registered on the same day / time for another client.

Having the Service Provider table with name and PK , Client with SocialSource and PK client_code and Scheduling with DateTime Start, End DateTime, FK_Polder, FK_Customer, and PK

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

    
asked by anonymous 14.03.2016 / 19:54

1 answer

0

You can create a check before doing 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' 
    
17.08.2016 / 19:37