How to check if there is scheduling in a time range?

Asked

Viewed 373 times

4

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

For example: Prestadordeservicos João will provide services to the customer condominium valley of the sun on the day March 15, 2016 from 10:00 to 12:00.

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

Having the table Service provider with name and PK cod_provider, Client with razaoSocial and PK cod_client and Scheduling with Datetime Inicio , Datetime Fim, Fk_prestador, Fk_cliente e PK cod_agendamento.

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

sql sql-server sql-server-2008

  • Using Trigger : http://stackoverflow.com/questions/1355921/sql-server-2008-help-writing-simple-insert-trigger, http://stackoverflow.com/questions/18502802/trigger-to-prevent-insertion-for-duplicate-data-of-two-columns

  • @Guilhermelautert if anyone tries to register John from 11:00 to 13:00 on March 15, 2016, as I know this time schedule conflicts with the 10:00 and 12:00 schedule of March 15, 2016?

  • 1

    http://stackoverflow.com/questions/11012099/query-to-check-overlapping-ranges-in-sql-server, no postgres serial overlaps (TIMESTAMP '2016-03-10 10:00:00', TIMESTAMP '2016-03-10 12:00:00') OVERLAPS (TIMESTAMP '2016-03-10 11:00:00', TIMESTAMP '2016-03-10 13:00:00')

  • @Guilhermelautert Excellent, Rigadão !!

1 answer

0

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

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'

Browser other questions tagged sql sql-server sql-server-2008

You are not signed in. Login or sign up in order to post.