Предположим, у меня есть следующая таблица в Sql Server 2008:
ItemId StartDate EndDate
1 NULL 2011-01-15
2 2011-01-16 2011-01-25
3 2011-01-26 NULL
Как видите, эта таблица имеет столбцы StartDate и EndDate. Я хочу проверить данные в этих столбцах. Интервалы не могут противоречить друг другу. Итак, приведенная выше таблица действительна, но следующая таблица недействительна, потому что первая строка имеет дату окончания больше, чем дата начала во второй строке.
ItemId StartDate EndDate
1 NULL 2011-01-17
2 2011-01-16 2011-01-25
3 2011-01-26 NULL
NULL
здесь означает бесконечность.
Не могли бы вы помочь мне написать сценарий для проверки данных?
[Второе задание]
Спасибо за ответы. У меня осложнение. Предположим, у меня есть такая таблица:
ItemId IntervalId StartDate EndDate 1 1 NULL 15.01.2011 2 1 2011-01-16 2011-01-25 3 1 2011-01-26 NULL 4 2 NULL 17.01.2011 5 2 2011-01-16 2011-01-25 6 2 2011-01-26 NULL
Here I want to validate intervals within a groups of IntervalId
, but not within the whole table. So, Interval 1 will be valid, but Interval 2 will be invalid.
And also. Is it possible to add a constraint to the table in order to avoid such invalid records?
[Final Solution]
I created function to check if interval is conflicted:
CREATE FUNCTION [dbo].[fnIntervalConflict]
(
@intervalId INT,
@originalItemId INT,
@startDate DATETIME,
@endDate DATETIME
)
RETURNS BIT
AS
BEGIN
SET @startDate = ISNULL(@startDate,'1/1/1753 12:00:00 AM')
SET @endDate = ISNULL(@endDate,'12/31/9999 11:59:59 PM')
DECLARE @conflict BIT = 0
SELECT TOP 1 @conflict = 1
FROM Items
WHERE IntervalId = @intervalId
AND ItemId <> @originalItemId
AND (
(ISNULL(StartDate,'1/1/1753 12:00:00 AM') >= @startDate
AND ISNULL(StartDate,'1/1/1753 12:00:00 AM') <= @endDate)
OR (ISNULL(EndDate,'12/31/9999 11:59:59 PM') >= @startDate
AND ISNULL(EndDate,'12/31/9999 11:59:59 PM') <= @endDate)
)
RETURN @conflict
END
And then I added 2 constraints to my table:
ALTER TABLE dbo.Items ADD CONSTRAINT
CK_Items_Dates CHECK (StartDate IS NULL OR EndDate IS NULL OR StartDate <= EndDate)
GO
and
ALTER TABLE dbo.Items ADD CONSTRAINT
CK_Items_ValidInterval CHECK (([dbo].[fnIntervalConflict]([IntervalId], ItemId,[StartDate],[EndDate])=(0)))
GO
I know, the second constraint slows insert and update operations, but it is not very important for my application.
Кроме того, теперь я могу вызывать функцию fnIntervalConflict
из кода моего приложения перед вставкой и обновлением данных в таблице.