使用 SQL 查找冲突的日期间隔 [英] Find conflicted date intervals using SQL
问题描述
假设我在 Sql Server 2008 中有下表:
ItemId StartDate EndDate1 NULL 2011-01-152 2011-01-16 2011-01-253 2011-01-26 无如您所见，此表具有 StartDate 和 EndDate 列.我想验证这些列中的数据.间隔不能相互冲突.因此，上表有效，但下一个表无效，因为第一行的结束日期大于第二行的开始日期.
ItemId StartDate EndDate1 NULL 2011-01-172 2011-01-16 2011-01-253 2011-01-26 无NULL 在这里表示无穷大.
你能帮我写一个数据验证的脚本吗?
【第二个任务】
感谢您的回答.我有一个并发症.让我们假设，我有这样的表:
<块引用>ItemId IntervalId StartDate EndDate1 1 NULL 2011-01-152 1 2011-01-16 2011-01-253 1 2011-01-26 无4 2 NULL 2011-01-175 2 2011-01-16 2011-01-256 2 2011-01-26 无这里我想验证一组 IntervalId 内的间隔，而不是整个表内的间隔.因此，区间 1 将有效，但区间 2 将无效.
还有.是否可以在表中添加约束以避免此类无效记录?
[最终解决方案]
我创建了检查间隔是否冲突的函数:
创建函数 [dbo].[fnIntervalConflict](@intervalId INT，@originalItemId INT，@startDate 日期时间，@endDate 日期时间)返回位作为开始SET @startDate = ISNULL(@startDate,'1/1/1753 12:00:00 AM')SET @endDate = ISNULL(@endDate,'12/31/9999 11:59:59 PM')声明@conflict BIT = 0选择顶部 1 @conflict = 1来自项目哪里IntervalId = @intervalIdAND ItemId <>@originalItemId和 ((ISNULL(StartDate,'1/1/1753 12:00:00 AM') >= @startDateAND ISNULL(StartDate,'1/1/1753 12:00:00 AM') <= @endDate)或 (ISNULL(EndDate,'12/31/9999 11:59:59 PM') >= @startDateAND ISNULL(EndDate,'12/31/9999 11:59:59 PM') <= @endDate))返回@冲突结尾然后我在表中添加了 2 个约束:
ALTER TABLE dbo.Items 添加约束CK_Items_Dates CHECK (StartDate IS NULL OR EndDate IS NULL OR StartDate <= EndDate)走和
ALTER TABLE dbo.Items 添加约束CK_Items_ValidInterval 检查 (([dbo].[fnIntervalConflict]([IntervalId], ItemId,[StartDate],[EndDate])=(0)))走我知道，第二个约束会减慢插入和更新操作的速度，但这对我的应用程序来说并不是很重要.而且，现在我可以在插入和更新表中的数据之前从我的应用程序代码中调用函数 fnIntervalConflict.
declare @T table (ItemId int, IntervalID int, StartDate datetime, EndDate datetime)插入@T选择 1, 1, NULL, '2011-01-15' union all选择 2, 1, '2011-01-16', '2011-01-25' union allselect 3, 1, '2011-01-26', NULL union all选择 4, 2, NULL, '2011-01-17' union all选择 5, 2, '2011-01-16', '2011-01-25' union all选择 6, 2, '2011-01-26', NULL选择 T1.*从@T 作为 T1内连接 @T 作为 T2在合并(T1.StartDate，'1753-01-01')<合并(T2.EndDate，'9999-12-31')和合并(T1.EndDate，'9999-12-31')>合并(T2.StartDate，'1753-01-01')和T1.IntervalID = T2.IntervalID 和T1.ItemId <>T2.ItemId结果:
ItemId IntervalID StartDate EndDate----------- ----------- --------------- -----------------------5 2 2011-01-16 00:00:00.000 2011-01-25 00:00:00.0004 2 NULL 2011-01-17 00:00:00.000Suppose I have following table in Sql Server 2008:
ItemId StartDate EndDate
1 NULL 2011-01-15
2 2011-01-16 2011-01-25
3 2011-01-26 NULL
As you can see, this table has StartDate and EndDate columns. I want to validate data in these columns. Intervals cannot conflict with each other. So, the table above is valid, but the next table is invalid, becase first row has End Date greater than StartDate in the second row.
ItemId StartDate EndDate
1 NULL 2011-01-17
2 2011-01-16 2011-01-25
3 2011-01-26 NULL
NULL means infinity here.
Could you help me to write a script for data validation?
[The second task]
Thanks for the answers. I have a complication. Let's assume, I have such table:
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
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.
And also, now I can call function fnIntervalConflict from my application code before inserts and updates of data in the table.
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
Result:
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
这篇关于使用 SQL 查找冲突的日期间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
