从日期范围中查找最大重叠周期 [英] Find maximum overlapping period from date ranges
问题描述
我希望最大日期范围相互重叠，如果期间没有碰撞其他日期范围而不是我想要的那样。
我有这张桌子：
I want maximum period of date range that is overlapping each other and if the period is not clashing other date ranges than i want it as it is.
I have this table :
CREATE TABLE [dbo].[table1](
[id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL
)
及其各自的价值：
And their respective values:
INSERT [dbo].[table1] VALUES ( CAST('2013-11-01 00:00:00.000' AS DateTime), CAST('2013-11-10 00:00:00.000' AS DateTime))
INSERT [dbo].[table1] VALUES ( CAST('2013-11-05 00:00:00.000' AS DateTime), CAST('2013-11-15 00:00:00.000' AS DateTime))
INSERT [dbo].[table1] VALUES ( CAST('2013-11-10 00:00:00.000' AS DateTime), CAST('2013-11-15 00:00:00.000' AS DateTime))
INSERT [dbo].[table1] VALUES ( CAST('2013-11-10 00:00:00.000' AS DateTime), CAST('2013-11-25 00:00:00.000' AS DateTime))
INSERT [dbo].[table1] VALUES ( CAST('2013-11-26 00:00:00.000' AS DateTime), CAST('2013-11-29 00:00:00.000' AS DateTime))
预期结果为：
ID     StartDate              EndDate
1       1-NOV-2013          2013年11月25日
2       2013年11月26日     2013年11月29日
提前致谢。
And expected result is :
ID StartDate EndDate
1 1-Nov-2013 25-Nov-2013
2 26-Nov-2013 29-Nov-2013
Thanks in advance.
推荐答案
阅读：
http://www.manning.com/nielsen/SampleChapter5.pdf [ ^ ]
希望这有帮助，
Read this:
http://www.manning.com/nielsen/SampleChapter5.pdf[^]
Hope this helps,
这适合您的样本数据 - 不知道它是否可以实时工作。它当然可以使用一些重构，但我没有时间。
祝你好运
This fits your sample data - don't know if it will work in real time. And it could certainly use some refactoring, but I didn't have time for that.
Good Luck
select ID2, MIN(LowStart) as LowStart, MAX(HighEnd) as HighEnd
from
(
select coalesce(b.id, a.id) ID2,
LowStart = case when a.StartDate < b.StartDate then
a.StartDate else coalesce(b.StartDate, a.StartDate) end,
HighEnd = case when a.EndDate > b.EndDate then
a.EndDate else coalesce(b.EndDate, a.EndDate) end
from table1 a left outer join table1 b
on a.id > b.id and
((b.StartDate BETWEEN a.StartDate AND a.EndDate) or
(b.EndDate BETWEEN a.StartDate and a.EndDate))
where coalesce(b.id, a.id) in
(
select ID1
from
(
select a.id as ID1, coalesce(b.id, a.id) ID2,
LowStart = case when a.StartDate < b.StartDate then
a.StartDate else
coalesce(b.StartDate, a.StartDate) end,
HighEnd = case when a.EndDate > b.EndDate then
a.EndDate else
coalesce(b.EndDate, a.EndDate) end
from table1 a left outer join table1 b
on a.id > b.id and
((b.StartDate BETWEEN a.StartDate AND a.EndDate) or
(b.EndDate BETWEEN a.StartDate and a.EndDate))
) t
where Id1 = id2
)
) t2
group by ID2
我想从一个例子开始：
I would like to start from an example:
DECLARE @tmp TABLE ([id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL)
INSERT INTO @tmp
VALUES ('2013-11-01','2013-11-10'),
('2013-11-05','2013-11-15'),
('2013-11-10','2013-11-15'),
('2013-11-10','2013-11-25'),
('2013-11-26','2013-11-29')
;WITH OverlappedDates AS
(
--initial values
SELECT id, StartDate, EndDate, id AS LastId, CONVERT(DATETIME, '1900-01-01') AS NewEndDate
FROM @tmp
----recurrent part
UNION ALL
SELECT od.id, od.StartDate, od.EndDate, t.Id AS LastId, t.EndDate AS NewEndDate
FROM OverlappedDates AS od INNER JOIN (
SELECT id, StartDate, EndDate
FROM @tmp) AS t ON od.Lastid +1 = t.id
WHERE od.EndDate BETWEEN t.StartDate AND t.EndDate
)
SELECT *
FROM OverlappedDates
WHERE LastId>id
ORDER BY id
返回：
Returns:
id StartDate EndDate LastId NewEndDate
1 2013-11-01 00:00:00.000 2013-11-10 00:00:00.000 2 2013-11-15 00:00:00.000
1 2013-11-01 00:00:00.000 2013-11-10 00:00:00.000 3 2013-11-15 00:00:00.000
1 2013-11-01 00:00:00.000 2013-11-10 00:00:00.000 4 2013-11-25 00:00:00.000
2 2013-11-05 00:00:00.000 2013-11-15 00:00:00.000 3 2013-11-15 00:00:00.000
2 2013-11-05 00:00:00.000 2013-11-15 00:00:00.000 4 2013-11-25 00:00:00.000
3 2013-11-10 00:00:00.000 2013-11-15 00:00:00.000 4 2013-11-25 00:00:00.000
如果您更换最后一个使用以下代码的SELECT 语句
If you replace last SELECT statement with below code
SELECT id, StartDate, MAX(LastId) AS LastId, MAX(NewEndDate) AS NewEndDate
FROM OverlappedDates
WHERE (LastId>Id AND NewEndDate>'1900-01-01') --(LastId=Id AND NewEndDate='1900-01-01') OR
GROUP BY id, StartDate
ORDER BY id, LastId
它会给：
it would give:
id StartDate LastId NewEndDate
1 2013-11-01 00:00:00.000 4 2013-11-25 00:00:00.000
2 2013-11-05 00:00:00.000 4 2013-11-25 00:00:00.000
3 2013-11-10 00:00:00.000 4 2013-11-25 00:00:00.000
得出结论的时间：
Time to draw conclusion:
- 如您所见，有多条日期重叠的记录。这意味着：
- id no。 1消耗ids 2,3，并以id no结束。 4
- id no。 2消耗id号。 3结束于id no。 4
- id no。 3结束于id no。 4
完成这项工作只有一件事：过滤器删除id为no的记录的数据。 2和3.那属于你;）
There is only one thing to do to accomplish this job: filter the data to remove records with id no. 2 and 3. That belongs to you ;)
这篇关于从日期范围中查找最大重叠周期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
