找出日期之间的间隔和重叠

Question

我正在构建一个报告，该报告将捕捉责任开始和结束日期中的所有差距和重叠。

想法是有一个列(状态(，它将有5个输出(我知道，一个记录可能有多个场景，但我需要按以下顺序分配它们：

1. 无责任-当根本没有关联的责任人时(ResponsibleId或Responsible Name为NULL(
2. 无当前责任-在与客户端关联的所有记录中，ResponsibleEndDate不为NULL(表示当前(
3. 重叠-上一个责任人的ResponsibleEndDate与下一个负责人的RescensibleStartDate重叠时
4. 差距-上一个负责人的ResponsibleEndDate和下一个负责人为Responsible StartDate之间存在差距
5. 有效-前一负责人的ResponsibleEndDate和下一负责人之间的RespontibleStartDate相差1天时

CREATE TABLE example
(ClientId INT, ClientName VARCHAR(100), ResponsibleId INT, ResponsibleName VARCHAR(100), ResponsibleStartDate DATE, ResponsibleEndDate DATE);
INSERT INTO example
VALUES
(123, 'John Smith', NULL, NULL, NULL, NULL),
(234, 'Thomas Anderson', 12345, 'Tom Cruise', '2019-04-13', '2020-09-15'),
(234, 'Thomas Anderson', 23456, 'John Travolta', '2020-09-16', '2022-01-15'),
(234, 'Thomas Anderson', 37890, 'Van Damm', '2022-01-16', NULL),
(345, 'Mary Tron', NULL, NULL, NULL, NULL),
(456, 'Jackie Chan', 56789, 'Leo Messi', '2018-05-18', '2022-01-18'),
(567, 'Cristiano Ronaldo', 12345, 'Tom Cruise', '2019-05-28', '2021-08-20'),
(567, 'Cristiano Ronaldo', 37890, 'Van Damm', '2021-07-15', '2022-01-15'),
(567, 'Cristiano Ronaldo', 17956, 'Harry Potter', '2022-01-25', NULL)
SELECT * FROM example

我需要的输出：

响应ID>响应结束日期状态1234Tom Cruise2020-09-15有效John Travolta2020-09-16有效37890Van Damm有效56789Leo Messi2022-01-18目前无责任12345汤姆·克鲁斯2019-05-282021-08-202021-07-152022-01-15差距179562022-01-25空<1td>差距

ClientId	客户端名称	响应名称	响应开始日期
123	John Smith	NULL	NULL
234	Thomas Anderson	2019-04-13
234	Thomas Anderson	23456	2022-01-15
234	Thomas Anderson	2022-01-16	NULL
345	Mary Tron	NULL	NULL[/td>	NULL		无责任
456	成龙	2018-05-18
567	克里斯蒂亚诺·罗纳尔多	重叠
567	克里斯蒂亚诺·罗纳尔多	37890	范达姆
567	克里斯蒂亚诺·罗纳尔多	哈利波特

这是一个良好的开端：

declare @example table
(ClientId INT, ClientName VARCHAR(100), ResponsibleId INT, ResponsibleName VARCHAR(100), ResponsibleStartDate DATE, ResponsibleEndDate DATE);
INSERT INTO @example
VALUES
(123, 'John Smith', NULL, NULL, NULL, NULL),
(234, 'Thomas Anderson', 12345, 'Tom Cruise', '2019-04-13', '2020-09-15'),
(234, 'Thomas Anderson', 23456, 'John Travolta', '2020-09-16', '2022-01-15'),
(234, 'Thomas Anderson', 37890, 'Van Damm', '2022-01-16', NULL),
(345, 'Mary Tron', NULL, NULL, NULL, NULL),
(456, 'Jackie Chan', 56789, 'Leo Messi', '2018-05-18', '2022-01-18'),
(567, 'Cristiano Ronaldo', 12345, 'Tom Cruise', '2019-05-28', '2021-08-20'),
(567, 'Cristiano Ronaldo', 37890, 'Van Damm', '2021-07-15', '2022-01-15'),
(567, 'Cristiano Ronaldo', 17956, 'Harry Potter', '2022-01-25', NULL)
;WITH CTE AS
(
SELECT *,
--ROW_NUMBER() OVER(PARTITION BY ClientName ORDER BY COALESCE(ResponsibleStartDate, ResponsibleEndDate)) DN,
LEAD(ResponsibleStartDate)OVER(PARTITION BY ClientName ORDER BY COALESCE(ResponsibleStartDate, ResponsibleEndDate)) NEXT_START_DATE,
LAG(ResponsibleEndDate)OVER(PARTITION BY ClientName ORDER BY COALESCE(ResponsibleStartDate, ResponsibleEndDate)) PREV_END_DATE
FROM @example
)
select *,
CASE 
WHEN ResponsibleName IS NULL THEN 'No Responsible'
WHEN PREV_END_DATE IS NOT NULL AND ResponsibleStartDate < PREV_END_DATE then 'OVERLAP'
WHEN PREV_END_DATE IS NOT NULL AND DATEDIFF(dd,PREV_END_DATE, ResponsibleStartDate) = 1 then 'VALID'
WHEN PREV_END_DATE IS NULL then 'VALID'
WHEN PREV_END_DATE IS NOT NULL AND ResponsibleStartDate > PREV_END_DATE then 'GAP'
ELSE 'CASE'
end [STATUS]
from 
CTE;