如何在SQL中按员工生成基于上一行的滚动ProjStart日期？

要实现按EmpName分区、Position排序，基于前一行的ProjStart日期加上前一行的EffortDays计算当前行ProjStart的需求，可以通过窗口函数实现累加计算，无需递归逻辑，具体实现如下：

DECLARE @aTestTable TABLE  
     (  
        Position int,  
        EmpName   VARCHAR(10),  
        ProjStart  DATE,
        EffortDays int
     );  
  
 INSERT INTO @aTestTable VALUES 
 (1, 'Adam', '2023-05-01',2),
 (2, 'Adam', NULL,3), 
 (3, 'Adam', NULL,1), 
 (4, 'Adam', NULL,2), 
 (5, 'Adam', NULL,4), 
 (6, 'Adam', NULL,3), 
 (1, 'Bill', '2023-05-07',4), 
 (2, 'Bill', NULL,5), 
 (3, 'Bill', NULL,1), 
 (4, 'Bill', NULL,3), 
 (5, 'Bill', NULL,6), 
 (1, 'Bob', '2023-05-10',4),
 (2, 'Bob', NULL,2),
 (3, 'Bob', NULL,5),
 (4, 'Bob', NULL,1),
 (5, 'Bob', NULL,1),
 (6, 'Bob', NULL,2),
 (7, 'Bob', NULL,3),
 (1, 'Dave', '2023-04-28',2),
 (2, 'Dave', NULL,1),
 (3, 'Dave', NULL,5),
 (4, 'Dave', NULL,4);

-- 核心查询语句
SELECT 
    Position,
    EmpName,
    DATEADD(
        DAY,
        COALESCE(
            SUM(EffortDays) OVER (PARTITION BY EmpName ORDER BY Position ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
            0
        ),
        FIRST_VALUE(ProjStart) OVER (PARTITION BY EmpName ORDER BY Position)
    ) AS ProjStart,
    EffortDays
FROM @aTestTable
ORDER BY EmpName, Position;

逻辑说明 #

1. FIRST_VALUE(ProjStart)：获取每个员工（EmpName分区）的第一个项目起始日期，作为递推计算的基准日期。
2. SUM(EffortDays) OVER (...)：计算当前行之前所有行（同员工、职位排序在前）的EffortDays总和，ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING限定累加范围为从分区第一行到当前行的前一行。
3. DATEADD：将基准日期加上累加的工作天数，得到当前行的项目起始日期；COALESCE处理第一行（无前置行）的累加值为0，直接返回基准日期。

执行上述查询后，将得到与预期完全一致的结果。

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