如何从给定日期范围的一组中找到重叠(公共)日期范围?需要考虑特定节目(PID)的所有事件(EID)的重叠(公共)日期范围。例如:节目(PID=13579)对于事件(EID=2)有两个日期范围。参考链接:link。已经查看过链接Link,但不实用。以下是示例模式和数据:
。
输出应该是:
![Image for Visualizing Overlapped (Common) Date Ranges](https://istack.dev59.com/VyL4f.webp)
CREATE TABLE #EventsTBL
(
PID INT,
EID INT,
StartDate DATETIME,
EndDate DATETIME
);
INSERT INTO #EventsTBL
VALUES
(13579, '1', '01 Jan 2018', '31 Mar 2019'),
(13579, '2', '01 Feb 2018', '31 May 2018'),
(13579, '2', '01 Jul 2018', '31 Jan 2019'),
(13579, '7', '01 Mar 2018', '31 Mar 2019'),
(13579, '5', '01 Feb 2018', '30 Apr 2018'),
(13579, '5', '01 Oct 2018', '31 Mar 2019'),
(13579, '8', '01 Jan 2018', '30 Apr 2018'),
(13579, '8', '01 Jun 2018', '31 Dec 2018'),
(13579, '13', '01 Jan 2018', '31 Mar 2019'),
(13579, '6', '01 Apr 2018', '31 May 2018'),
(13579, '6', '01 Sep 2018', '30 Nov 2018'),
(13579, '4', '01 Feb 2018', '31 Jan 2019'),
(13579, '19', '01 Mar 2018', '31 Jul 2018'),
(13579, '19', '01 Oct 2018', '28 Feb 2019'),
--
(13570, '16', '01 Feb 2018', '30 Jun 2018'),
(13570, '16', '01 Aug 2018', '31 Aug 2018'),
(13570, '16', '01 Oct 2018', '28 Feb 2019'),
(13570, '23', '01 Mar 2018', '30 Jun 2018'),
(13570, '23', '01 Nov 2018', '31 Jan 2019');
输出应该是:
PID StartDate EndDate
13579 01-Apr-2018 30-Apr-2018
13579 01-Oct-2018 30-Nov-2018
13570 01-Mar-2018 30-Jun-2018
13570 01-Nov-2018 31-Jan-2019