sql server - una - Restricción de comprobación de superposición de rango de fechas

sintaxis check sql server

Question

Tengo una tabla simple en SQL Server 2005 con 3 columnas: DateStart, DateEnd y Value. Intenté establecer una "restricción de comprobación de tabla" para evitar insertar registros superpuestos. Por ejemplo, si en dicha tabla hay un registro con DateStart = 2012-01-01 (primer enero) y DateEnd 2012-01-15 (15 de enero) que la restricción de verificación debe evitar insertar un registro con DateStart = 2012-01-10 ( sin atención DateEnd), un registro con DateEnd = 2012-01-10 (sin atención DateStart) o un registro con DateStart 2011-12-10 y DateEnd 2012-02-01.

Definí un UDF de tal manera:

CREATE FUNCTION [dbo].[ufn_checkOverlappingDateRange]
(
    @DateStart AS DATETIME
    ,@DateEnd AS DATETIME
)
RETURNS BIT 
AS
BEGIN
  DECLARE @retval BIT
  /* date range at least one day */
  IF (DATEDIFF(day,@DateStart,@DateEnd) < 1)
    BEGIN
      SET @retval=0
    END
  ELSE
    BEGIN
      IF EXISTS
        (
          SELECT
              *
            FROM [dbo].[myTable]
            WHERE
            ((DateStart <= @DateStart) AND (DateEnd > @DateStart))
            OR
            ((@DateStart <= DateStart) AND (@DateEnd > DateStart))
        )
        BEGIN
          SET @retval=0
        END
    ELSE
      BEGIN
            SET @retval=1
          END
        END
  RETURN @retval
END

Entonces el cheque de pensamiento podría ser esto:

ALTER TABLE [dbo].[myTable]  WITH CHECK ADD  CONSTRAINT [CK_OverlappingDateRange] CHECK  ([dbo].[ufn_checkOverlappingDateRange]([DateStart],[DateEnd])<>(0))

Pero incluso con [myTable] el operador EXISTS vacío devuelve verdadero cuando inserto el primer registro. ¿Dónde estoy wrog? ¿Es posible establecer una restricción como esta?

Por cierto, considero que DateStart incluye en rango y DateEnd excluye del rango.

Answer 1

El CHECK se está ejecutando después de que la fila se haya insertado, por lo que el rango se superpone a sí mismo.

Tendrá que modificar su DÓNDE para incluir algo como: @MyTableId <> MyTableId .

Por cierto, su expresión WHERE se puede simplificar.

Los rangos no se superponen si:

• El final de un rango es antes del comienzo del otro.
• o el comienzo de un rango es después del final del otro.

Que se podría escribir en SQL como:

WHERE @DateEnd < DateStart OR DateEnd < @DateStart

Niega eso para obtener los rangos que se superponen ...

WHERE NOT (@DateEnd < DateStart OR DateEnd < @DateStart)

... que según las leyes de De Morgan es lo mismo que ...

WHERE (NOT (@DateEnd < DateStart) AND NOT (DateEnd < @DateStart))

... que es lo mismo que

WHERE @DateEnd >= DateStart AND DateEnd >= @DateStart

Así que tu final DONDE debería ser:

WHERE
    @MyTableId <> MyTableId
    AND @DateEnd >= DateStart
    AND DateEnd >= @DateStart

[SQL Fiddle]

NOTA: para permitir que los rangos "toquen", use <= en la expresión inicial, lo que produciría ''>'' en la expresión final.

Answer 2

Simplemente me gustaría agregar a la Respuesta de el caso en el que el DateEnd es nulo ya que actualmente tengo tal escenario.

Esto puede ocurrir cuando mantienes registros de pinchazos y el usuario todavía está conectado.

WHERE
    @MyTableId <> MyTableId
    AND @DateEnd >= DateStart
    AND DateEnd >= @DateStart
    OR @DateEnd >= DateStart
    AND DateEnd is null
    OR @DateStart >= DateStart
    AND DateEnd is null

No sé qué tan bien esta consulta es el rendimiento, estoy seguro de que hay formas de optimizarla.