在 SQL 查询中创建滚动日期

我正在try 创建一个针对其自身结果运行的查询，直到结果满足我所寻找的结果.我需要编写SQL来告诉我下一行的第[ProjStart]个日期应该是基于前一行的第[ProjStart]个日期+前面的第[EffortDays]行，每个第[EmpName]行.每个任务的顺序基于由[EmpName]划分的[Position]列.诀窍在于，在查询告诉它具有前一行的[ProjStart]+[EffortDays]之前，并不是所有行都有日期.

以下是帮助创建所需结果的示例表

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 * FROM @aTestTable

如果我能提供澄清，请告诉我.

下面是预期的输出，取前面的行[ProjStart]并添加当前行[ProjStart]的前面的行[EffortDays]，当前行[ProjStart]按[EmpName]分区并按[Position]排序.

Position    EmpName ProjStart   EffortDays
1   Adam    2023-05-01  2
2   Adam    2023-05-03  3
3   Adam    2023-05-06  1
4   Adam    2023-05-07  2
5   Adam    2023-05-09  4
6   Adam    2023-05-13  3
1   Bill    2023-05-07  4
2   Bill    2023-05-11  5
3   Bill    2023-05-16  1
4   Bill    2023-05-17  3
5   Bill    2023-05-20  6
1   Bob 2023-05-10  4
2   Bob 2023-05-14  2
3   Bob 2023-05-16  5
4   Bob 2023-05-21  1
5   Bob 2023-05-22  1
6   Bob 2023-05-23  2
7   Bob 2023-05-25  3
1   Dave    2023-04-28  2
2   Dave    2023-04-30  1
3   Dave    2023-05-01  5
4   Dave    2023-05-06  4