Ограничение проверки перекрытия диапазона дат

У меня простая таблица в SQL Server 2005 с тремя столбцами: DateStart, DateEnd и Value. Я попытался установить "ограничение проверки таблицы", чтобы избежать вставки перекрывающихся записей. Например, если в такой таблице есть запись с DateStart = 2012-01-01 (первый январь) и DateEnd 2012-01-15 (15 января), чем в контрольном ограничении, необходимо избегать вставки записи с DateStart = 2012-01-10 ( no care DateEnd), запись с DateEnd = 2012-01-10 (no care DateStart) или запись с DateStart 2011-12-10 и DateEnd 2012-02-01.

Я определил UDF таким образом:

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

Тогда проверка мысли может быть такой:

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

Но даже с [myTable] пустым EXISTS Operator возвращает true, когда я вставляю первую запись. Где я wrog? Можно ли установить ограничение, подобное этому?

BTW Я считаю, что DateStart включает в диапазон, а DateEnd исключает из диапазона.

Ответ 1

CHECK выполняется после, строка вставлена, поэтому диапазон перекрывается с самим собой.

Вам нужно внести изменения в WHERE, чтобы включить что-то вроде: @MyTableId <> MyTableId.

Кстати, ваше выражение WHERE может быть упрощено.

Диапазоны не перекрываются, если:

конец одного диапазона перед началом другого
или начало одного диапазона после окончания другого.

Что можно записать в SQL, например:

WHERE @DateEnd < DateStart OR DateEnd < @DateStart

Отмените это, чтобы получить диапазоны, которые делают перекрываются...

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

... который согласно законы Де Моргана - это то же самое, что...

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

..., что совпадает с:

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

Итак, ваш последний ГДЕ должен быть:

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

[SQL Fiddle]

ПРИМЕЧАНИЕ. Чтобы позволить диапазонам "касаться", используйте <= в стартовом выражении, которое создало бы " > " в конечном выражении.

Ответ 2

Я просто хотел бы добавить в ответ Branko Dimitrijevic случай, когда DateEnd имеет значение null, поскольку в настоящее время у меня такой сценарий.

Это может произойти, если вы сохраняете пунш в журналах, и пользователь все еще регистрируется.

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

Я не знаю, насколько хорошо этот запрос имеет производительность, я уверен, что есть способы его оптимизации.