如何在SQL中基于列匹配值计算行之间的DATEDIFF

针对你的需求，我们需要把每个状态（包括初始状态和最终状态）的持续时间都计算出来，核心是把状态变更记录拆分成每个状态的时间区间，再计算区间差值。

问题分析 #

你的CTE记录的是状态变更事件：每条记录的Date是状态变更的日期，变更前是InitialVal，变更后是EndVal。之前用LAG/LEAD没得到预期结果的原因：

• LAG只能获取上一条记录的日期，会漏掉初始状态（第一条记录的InitialVal）的时长；
• LEAD如果计算逻辑搞反（用当前日期减下一个日期）会得到负数，且最后一条记录没有后续日期，会漏掉最终状态的时长。

解决方案（以SQL Server为例） #

我们可以通过拆分初始状态和变更后状态，结合LEAD获取下一次变更日期，同时处理最终状态的结束时间（用当前日期）：

WITH OriginalCTE AS (
    -- 替换成你的实际CTE
    SELECT 100 AS ID, 'Status' AS Type, '2023-04-18' AS Date, 'Pending Review' AS InitialVal, 'Need Signature' AS EndVal UNION ALL
    SELECT 100, 'Status', '2023-10-03', 'Need Signature', 'In Progress' UNION ALL
    SELECT 100, 'Status', '2024-01-02', 'In Progress', 'Testing' UNION ALL
    SELECT 100, 'Status', '2024-05-04', 'Testing', 'Completed' UNION ALL
    SELECT 101, 'State', '2023-08-29', 'Inactive', 'Active' UNION ALL
    SELECT 101, 'State', '2023-10-14', 'Active', 'Inactive'
),
OrderedChanges AS (
    SELECT 
        ID, Type, Date, InitialVal, EndVal,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS rn,
        COUNT(*) OVER (PARTITION BY ID) AS total_rows
    FROM OriginalCTE
),
StatePeriods AS (
    -- 提取初始状态（第一条记录的InitialVal）
    SELECT 
        ID, Type,
        InitialVal AS State,
        NULL AS StartDate, -- 若有初始创建日期，替换为该字段
        CAST(Date AS DATE) AS EndDate
    FROM OrderedChanges
    WHERE rn = 1

    UNION ALL

    -- 提取每次变更后的状态（EndVal）
    SELECT 
        ID, Type,
        EndVal AS State,
        CAST(Date AS DATE) AS StartDate,
        -- 下一次变更日期，最后一条记录用当前日期作为结束
        CASE 
            WHEN rn < total_rows THEN LEAD(Date) OVER (PARTITION BY ID ORDER BY Date)
            ELSE CAST(GETDATE() AS DATE)
        END AS EndDate
    FROM OrderedChanges
)
SELECT 
    ID, Type, State,
    -- 计算时长，处理初始状态无起始日期的情况
    CASE 
        WHEN StartDate IS NULL THEN '未知起始时间，时长：' + CAST(DATEDIFF(day, '1900-01-01', EndDate) AS VARCHAR) + ' 天（默认起始点）'
        ELSE CAST(DATEDIFF(day, StartDate, EndDate) AS VARCHAR) + ' 天'
    END AS Duration
FROM StatePeriods
ORDER BY ID, StartDate;

关键说明 #

1. 初始状态处理：如果你的业务中有每个ID的创建日期（即初始状态的开始时间），把NULL AS StartDate替换为实际的创建日期字段即可，这样初始状态的时长计算会更准确。
2. 最终状态处理：用GETDATE()作为最后一个状态的结束时间，如果你有业务上的结束日期，替换成对应的字段即可。
3. 时间单位：示例中用DATEDIFF(day, ...)计算天数，你可以根据需求换成hour/month等其他单位。

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