这是一个间隙和孤岛问题，对于每个员工，您需要确定Critical_Task=1（孤岛）所在的连续行组和Critical_Task=0的连续行（间隙）所在的组。之后，您可以使用count窗口函数对定义的组中每个员工的Critical_Task=1处的连续行进行计数。

SELECT Employee, Day, Task_List, Critical_Task, 
       COUNT(CASE WHEN Critical_Task=1 THEN Task_List END) OVER 
       (PARTITION BY Employee, grp ORDER BY Day) Consec_Days_Crit_Task
FROM
(
  SELECT *,
     ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Day) -
     ROW_NUMBER() OVER (PARTITION BY Employee,Critical_Task ORDER BY Day) AS grp
  FROM Table_Name
) T
ORDER BY Employee DESC, Day

ROW_NUMBER() OVER ... AS grp用于识别所需的组。
请参阅demo。

我们可以在同一数据上留下连接，以确定非关键任务限制当前记录的最长日期。然后，只需使用DATEDIFF()函数计算自上一个0开始的连续天数。
我已经使用了前四列的示例数据，并将它们插入到表变量中，以便在下面的示例中使用：

DECLARE @Data as TABLE (
  Employee varchar(50), 
  Day date, 
  Task_List varchar(50), 
  Critical_Task bit)

INSERT INTO @Data
VALUES ('Tom J', '10/1/22', 'Sweep, Lock Door*', 1)
  ,('Tom J', '10/2/22', 'Sweep, Lock Door*', 1)
  ,('Tom J', '10/3/22', 'Mop, Dishes', 0)
  ,('Tom J', '10/4/22', 'Sweep, Lock Door*', 1)
  ,('Sue B', '10/1/22', 'Mop, Dishes', 0)
  ,('Sue B', '10/2/22', 'Mop, Dishes', 0)
  ,('Sue B', '10/3/22', 'Sweep, Lock Door*', 1)
  ,('Sue B', '10/4/22', 'Mop, Dishes', 0);

SELECT Employee, Day, Task_List, Critical_Task
  , DATEDIFF(day, last_0, Day) as Consec_Days_Crit_Task
FROM (
    SELECT a.*
        , coalesce(max(b.Day), 
            dateadd(day, -1, min(a.Day) over(partition by a.employee))) as last_0
    FROM @Data as a
    LEFT JOIN @Data as b
        ON a.Employee = b.Employee AND b.Critical_Task = 0 AND a.Day >= b.Day
    GROUP BY a.Employee, a.Day, a.Task_List, a.Critical_Task
) as a;