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.