使用SQL查找有冲突的日期间隔

Question

假设我在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列。我想验证这些列中的数据。间隔不能相互冲突。因此，上面的表是有效的，但是下一个表是无效的，因为第一行的结束日期大于第二行的StartDate。

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        2011-01-15
2       1          2011-01-16  2011-01-25
3       1          2011-01-26  NULL
4       2          NULL        2011-01-17
5       2          2011-01-16  2011-01-25
6       2          2011-01-26  NULL

在这里，我想验证一组中的间隔IntervalId，但不验证整个表中的间隔。因此，间隔1将是有效的，但是间隔2将是无效的。

并且。可以在表中添加约束以避免此类无效记录吗？

[最终解决方案]

我创建了一个函数来检查间隔是否冲突：

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

然后我在表中添加了2个约束：

ALTER TABLE dbo.Items ADD CONSTRAINT
    CK_Items_Dates CHECK (StartDate IS NULL OR EndDate IS NULL OR StartDate <= EndDate)

GO

和

ALTER TABLE dbo.Items ADD CONSTRAINT
    CK_Items_ValidInterval CHECK (([dbo].[fnIntervalConflict]([IntervalId], ItemId,[StartDate],[EndDate])=(0)))

GO

我知道，第二个约束减慢了插入和更新操作的速度，但这对我的应用程序不是很重要。而且，现在我可以fnIntervalConflict在插入和更新表中的数据之前从应用程序代码中调用函数。

Answer 1

declare @T table (ItemId int, IntervalID int, StartDate datetime,   EndDate datetime)

insert into @T
select 1, 1,  NULL,        '2011-01-15' union all
select 2, 1, '2011-01-16', '2011-01-25' union all
select 3, 1, '2011-01-26',  NULL        union all
select 4, 2,  NULL,        '2011-01-17' union all
select 5, 2, '2011-01-16', '2011-01-25' union all
select 6, 2, '2011-01-26',  NULL

select T1.*
from @T as T1
  inner join @T as T2
    on coalesce(T1.StartDate, '1753-01-01') < coalesce(T2.EndDate, '9999-12-31') and
       coalesce(T1.EndDate, '9999-12-31') > coalesce(T2.StartDate, '1753-01-01') and
       T1.IntervalID = T2.IntervalID and
       T1.ItemId <> T2.ItemId

结果：

ItemId      IntervalID  StartDate               EndDate
----------- ----------- ----------------------- -----------------------
5           2           2011-01-16 00:00:00.000 2011-01-25 00:00:00.000
4           2           NULL                    2011-01-17 00:00:00.000