SQL Server如何计算记录间重叠时间段的具体时长

你可以先通过CTE预读取上一行的起止时间，避免重复调用LAG函数，再基于重叠区间的通用计算逻辑得到精确时长，同时你原来的重叠判断逻辑可以大幅简化，无需编写多分支OR条件。

完整实现代码 #

WITH LagTimeCTE AS (
    SELECT 
        *,
        LAG([StartDateTime], 1) OVER (ORDER BY [aaID]) AS PrevStart,
        LAG([EndDateTime], 1) OVER (ORDER BY [aaID]) AS PrevEnd
    FROM 你的业务表名 -- 替换为你实际的表名即可
)
SELECT 
    你需要返回的其他字段,
    [StartDateTime],
    [EndDateTime],
    CASE 
        WHEN PrevStart IS NULL THEN NULL
        WHEN OverlapStart < OverlapEnd THEN 'yes'
        ELSE 'no'
    END AS [OverLapping with Previous],
    CASE 
        WHEN PrevStart IS NULL THEN NULL
        WHEN OverlapStart < OverlapEnd THEN DATEDIFF(minute, OverlapStart, OverlapEnd) 
        ELSE 0
    END AS [OverlapDuration_Minutes]
FROM (
    SELECT 
        *,
        -- 计算重叠区间边界：左边界取两个开始时间的最大值，右边界取两个结束时间的最小值
        CASE WHEN [StartDateTime] > PrevStart THEN [StartDateTime] ELSE PrevStart END AS OverlapStart,
        CASE WHEN [EndDateTime] < PrevEnd THEN [EndDateTime] ELSE PrevEnd END AS OverlapEnd
    FROM LagTimeCTE
) AS CalcTbl

逻辑说明 #

• 两个时间段存在重叠的充要条件为：两个时间段的最大开始时间 < 两个时间段的最小结束时间，该规则覆盖所有重叠场景，完全替代你原有多OR判断的逻辑，出错概率更低。
• 时间单位可自定义：如果需要秒级时长将DATEDIFF第一个参数改为second，需要小时改为hour，需要天数改为day，返回值即为对应单位的精确重叠时长，同时可同步修改结果字段名方便识别。

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