输入新范围时自动调整日期范围， 使用 SQL

• 下载源代码 - 873 B

引言

本文描述了我关于如何保持日期范围不重叠的解决方案。我将使用 SQL 而不是伪代码来使代码更实用，但使用不同的语言编写它也应该很容易。

问题

存储时间或基于时间的_data的一种方法是为其分配一个日期范围。例如，产品价格表。价格会存储在该价格生效的日期范围。

日期范围的一个常见问题是如何使其不重叠。例如，对于示例产品价格表，如果存在两个重叠的范围，则数据（在这种情况下是价格）将变得含糊不清。例如。如果表中包含一条记录 2009 年 1 月 1 日-2009 年 1 月 30 日，10 美元；另一条记录 2009 年 1 月 15 日-2009 年 3 月 31 日，15 美元；那么 2009 年 1 月 16 日的价格是多少？

一个解决方案

一种策略是禁止输入将与现有范围重叠的范围。这意味着在输入新数据之前，现有范围需要先手动调整。这至少很麻烦，最糟糕的是容易出错。

我偏好的解决方案

我的假设是，输入的数据比现有数据更有效，因此用户不必为调整而烦恼。

那么我偏好的策略是允许输入将重叠的范围（UI），然后自动化调整（SQL），最后插入输入范围（SQL）。我们该如何做到这一点？

示例数据

让我们从一个示例表开始

CREATE TABLE [dbo].[test](
[Start] [datetime] NULL,
[End] [datetime] NULL,
[data] [nvarchar](50) NULL
)

和一些数据

INSERT INTO [dbo].[test] VALUES ('Jan 1, 2009','Jan 12, 2009','A')
INSERT INTO [dbo].[test] VALUES ('Jan 13, 2009','Jan 25, 2009','B')
INSERT INTO [dbo].[test] VALUES ('Feb 1, 2009','Feb 28, 2009','C')
INSERT INTO [dbo].[test] VALUES ('Mar 12, 2009','Mar 13, 2009','D')

查询该表将得到以下结果

Start      End        data
---------- ---------- ----
2009-01-01 2009-01-12 A
2009-01-13 2009-01-25 B
2009-02-01 2009-02-28 C
2009-03-12 2009-03-13 D

情况 1、2 和 3

给定以上数据，如果用户输入以下数据会发生什么？

如果我们查看日期，我们会看到新范围将与所有现有范围重叠，但方式并非完全相同。

情况 1：新范围与现有范围的开始和结束重叠

这对于数据 B 和 C 来说是正确的。它们不再有效，因此我们将其删除。

-- 1 delete overlaps
delete from [dbo].[test]
where start>=@start
and [end]<=@end

情况 2：新范围仅与现有范围的结束重叠

这对于 A 是正确的。范围不再有效，但对于开始到新范围开始 - 1，因此结束将被调整。

请注意，下面的代码假定上面的代码已经运行。如果没有，那么我们需要调整 where 条件以明确排除其开始日期也包含在新范围内的范围。

-- 2 update pred
update [dbo].[test]
set [end]=@start-1
where [end]>=@start
and [end]<=@end

情况 3：新范围仅与现有范围的开始重叠

这对于 D 是正确的。范围开始需要调整到新范围结束 + 1。

请注意，这也假定它将与上面的代码一起使用。如果没有，那么必须排除结束日期也包含在新范围内的范围。

-- 3 update suc
update [dbo].[test]
set start=@end+1
where start>=@start
and start<=@end

运行三个代码后，我们将插入新范围

INSERT INTO [dbo].[test] VALUES (@start,@end,@data)

现在查询该表将得到以下结果

Start      End        data
---------- ---------- ----
2009-01-01 2009-01-01 A
2009-01-02 2009-03-12 E
2009-03-13 2009-03-13 D

情况 4：新范围位于现有范围之内

那么，如果新范围位于现有范围之内怎么办？让我们从以上数据开始，然后处理以下数据。

set @start='Jan 10, 2009'
set @end='Mar 1, 2009'
set @data='F'

范围 F 位于之前添加的范围 E 之内。这将使中间日期无效。我们可以通过将日期范围 E 分为两部分来纠正此问题，使用新范围内的中间点，然后应用情况 2 和 3。或者我们可以这样做。

复制具有调整后结束日期的旧范围

--split 1
insert into [dbo].[test]
select Start,@start-1,data
from [dbo].[test]
where start<@start
and [end]>@end

调整旧范围的开始日期

--split 2
update [dbo].[test]
set start=@end+1
where start<@start
and [end]>@end

最后，插入新范围

INSERT INTO [dbo].[test] VALUES (@start,@end,@data)

现在查询将得到

Start      End        data
---------- ---------- ----
2009-01-01 2009-01-01 A
2009-01-02 2009-01-09 E
2009-01-10 2009-03-01 F
2009-03-02 2009-03-12 E
2009-03-13 2009-03-13 D

Using the Code

附加的 zip 文件包含从创建表到选择结果数据的所有查询。对于实际使用，我建议创建一个存储过程，并将新范围和数据作为参数传入。

关注点

我敢肯定，这个问题以前已经解决过，因为它并不是一个真正新问题，但我没有找到任何类似的帖子（或者我搜索得不够熟练 :)）。如果您找到任何相关内容，请随时提供链接。

一些 DBA 可能更喜欢将数据标记为删除而不是永久删除（即清除）。而另一些 DBA 可能更喜欢将无效数据保留为历史数据。

另一个可以想到允许历史数据的方法是允许重叠并记录输入日期。我认为这足以获取最新价格，即具有最新输入日期的匹配范围，但我将在另一篇文章中讨论这个问题 ;)。

历史

• 创建于 2009 年 9 月 24 日，作者 acarpio1975
• 编辑于 2009 年 9 月 28 日，作者 acarpio1975