SQL Server中按设备分组调整相邻合同记录结束日期的实现

需求描述 #

现有合同数据集包含多台不同编号的设备，每台设备对应多条合同记录，每条记录存储合同编号Ref、设备编号Equipment、合同生效日期start_date、合同结束日期end_date字段。
需要实现通用处理逻辑：

• 按设备分组，对组内合同按生效日期升序排序
• 若相邻两条合同中，上一条的结束日期晚于下一条的生效日期（存在日期重叠），则将上一条记录的new_end_date设置为下一条记录生效日期减1天
• 若不存在日期重叠，或是设备下的最后一条合同记录，new_end_date保持原始结束日期值不变

原有硬编码代码仅能处理固定Ref的两条记录，无法适配全量分组数据。

实现思路 #

使用T-SQL窗口函数LEAD()替代固定子查询：

• 按Equipment字段分区，按start_date字段排序，直接获取每条合同记录对应的下一条同设备合同的生效日期
• 通过CASE判断日期重叠关系，计算修正后的结束日期，无需硬编码特定记录编号，可适配任意数量的设备和合同记录。

完整可运行代码 #

-- 测试表构造（与提供的样例数据一致）
DECLARE @Test TABLE
(
    Ref VARCHAR(10),
    Equipment VARCHAR(10),
    start_date DATE,
    end_date DATE
)
INSERT INTO @Test VALUES ('1290','9999','2014-03-01','2016-04-16')
INSERT INTO @Test VALUES ('1380','9999','2016-04-01','2018-05-17')
INSERT INTO @Test VALUES ('2000','9999','2018-05-01','2020-06-27')
INSERT INTO @Test VALUES ('2900','9999','2020-06-01','2021-06-29')
INSERT INTO @Test VALUES ('1556','8888','2016-01-01','2017-02-27')
INSERT INTO @Test VALUES ('1876','8888','2017-02-01','2018-04-26')
INSERT INTO @Test VALUES ('2897','8888','2018-04-01','2020-03-30')
INSERT INTO @Test VALUES ('2653','7777','2017-09-01','2018-10-14')
INSERT INTO @Test VALUES ('4536','7777','2018-10-01','2019-11-13')
INSERT INTO @Test VALUES ('2987','7777','2019-11-01','2020-12-27')
INSERT INTO @Test VALUES ('2776','7777','2020-12-01','2021-11-30')

-- 核心处理逻辑
SELECT
    Ref,
    Equipment,
    start_date,
    end_date,
    CASE
        WHEN end_date > NextStartDate THEN DATEADD(DAY, -1, NextStartDate)
        ELSE end_date
    END AS new_end_date
FROM (
    SELECT
        *,
        LEAD(start_date) OVER (PARTITION BY Equipment ORDER BY start_date) AS NextStartDate
    FROM @Test
) t
ORDER BY Equipment, start_date

效果说明 #

执行代码后输出结果与预期完全一致：

• 自动适配所有设备分组，无需手动指定合同编号
• 所有存在重叠的相邻合同，上一条的结束日期会自动修正为下一条生效日期的前1天
• 每台设备的最后一条合同无后续记录，保留原始结束日期

内容的提问来源于stack exchange，提问作者Riad