Events overlapping times validation in a stored procedure

Question

I need help, I have a stored procedure for inserting an event that is displayed in a calendar, what I intend to do is validate if I have an event in ID_SALA 2 or ID_SALA 3 on the same date should not have the same start and end times.

My ID_SALA has only has 2 rows:

ID_SALA | Name
 2      | AUDITORIO
 3      | SALA VIRTUAL

My format date is: YYYY/MM/DD

My begin and end parameters are:

  @START_TIME NVARCHAR(5) i insert this 11:00
  @END_TIME NVARCHAR(5)   i insert this 12:00

Here is an image of the event calendar

@DESCRIPCION NVARCHAR(500),
@ID_SALA INT,
@ID_TIPO_SOLICITANTE INT,
@DATE SMALLDATETIME,
@START_TIME NVARCHAR(5),
@END_TIME NVARCHAR(5),
@ID_PRESTATARIO INT,
@ID_USUARIO INT,
@SOLICITANTE NVARCHAR(150),
@AUTORIZACION INT

AS
SET NOCOUNT ON

DECLARE @ID_PERIODO INT

SELECT @ID_PERIODO = ID_PERIODO FROM CAT_PERIODOS WHERE ACTIVO = 1


INSERT INTO PRESTAMO_SALA
    (DESCRIPCION, ID_SALA, ID_TIPO_SOLICITANTE, FECHA_PRESTAMO, HORA_INICIO, HORA_FIN, ID_PRESTATARIO, 

ID_USUARIO, ID_PERIODO, FECHA_REG, SOLICITANTE, ID_AUTORIZACION)
VALUES
    (@DESCRIPCION, @ID_SALA, @ID_TIPO_SOLICITANTE, @DATE, @START_TIME, @END_TIME, @ID_PRESTATARIO, @ID_USUARIO, 

@ID_PERIODO, GETDATE(), @SOLICITANTE, @AUTORIZACION)

SELECT @@IDENTITY AS ID_PRESTAMO_SALA

Sorry for my English

Are you trying to check if two events in the same location have overlapping time? — Jose Chama, Jul 16 '14 at 17:41
Check this out http://stackoverflow.com/questions/117962/simplest-efficient-way-to-find-rows-with-time-interval-overlaps-in-sql — Jose Chama, Jul 16 '14 at 17:46

score 0 · Accepted Answer · answered Jul 16 '14 at 17:54

You can add a check at the top of the stored procedure to see if there is an event in that location that overlaps the time with the new event.

IF EXISTS (
  SELECT 1
  FROM PRESTAMO_SALA PS
  WHERE PS.START_TIME <= @END_TIME 
      AND PS.END_TIME >= @START_TIME 
      AND PS.[DATE] = @DATE
      AND PS.ID_SALA = @ID_SALA 
      )

    ... RAISE ERROR ...

Events overlapping times validation in a stored procedure

1 Answers1