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 Server查找重叠的日期范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!