SQL Server查找重叠的日期范围 [英] SQL Server find overlaping date ranges

问题描述

我每年都有一个日期范围（季节）的表格。
正常的是，一个季节的结束是下个季节的开始。
在下面的例子中，我以粗体显示了两个不规则的季节设置。第一季第一季结束是第二季度开始的第二天。第二季第四季开始是海龙3结束后的一天。3 b
$ b

I have a table with date ranges (seasons) for each year. The normal is that the end of the one season is the beggining of the next season. In the example below I have in bold the two irregular season setups. In the first the end of season 1 is a day after the beggining of season 2. In the second, the beggining of season 4 is one day after the end of seaon 3

+--------+----+--------------+--------------+
|SEASONID|YEAR|DATE FROM     |DATE TO       |
+--------+----+--------------+--------------+
|1       |14  |  2014-01-01  |**2014-01-31**|
|2       |14  |**2014-01-30**|  2014-03-01  |
|3       |14  |  2014-03-01  |**2014-05-22**|
|4       |14  |**2014-05-23**|  2014-10-16  |
|5       |14  |  2014-10-16  |  2014-12-01  |
+--------+----+--------------+--------------+

有没有办法编写可以捕获未正确设置的季节的查询？ （一个季节结束不是下一个季节的开始）

Is there a way to write a query that can capture the seasons that are not correctly setup? (the ones that the end of one season is not the beginning of the next)

推荐答案

这回答了你的一半问题：use来自这篇文章的重叠日期查询查找有冲突的记录：

This answers half of your question: use the overlapping date queries from this article to find conflicting records:

-- 1.2) select date ranges that overlap [d1, d2) (d2 and end_date are exclusive)
-- SELECT * FROM <table> WHERE @d2 > start_date AND end_date > @d1

SELECT s1.*
FROM seasons AS s1
INNER JOIN seasons AS s2 ON s1.seasonid <> s2.seasonid
AND s2.date_to > s1.date_from
AND s1.date_to > s2.date_from

结果：

+--------+----+----------+----------+--------+----+----------+----------+
|seasonid|year|date_from |date_to   |seasonid|year|date_from |date_to   |
+--------+----+----------+----------+--------+----+----------+----------+
|1       |14  |2014-01-01|2014-01-31|2       |14  |2014-01-30|2014-03-01|
+--------+----+----------+----------+--------+----+----------+----------+
|2       |14  |2014-01-30|2014-03-01|1       |14  |2014-01-01|2014-01-31|
+--------+----+----------+----------+--------+----+----------+----------+

SQL Fiddle

这篇关于SQL Server查找重叠的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！