员工跨项目有效工作日计算及SQL查询修正需求

统计规则 #

• 员工转至其他项目时，若前后项目间隔超过90天，则此前所有工作日不计入统计，仅统计间隔后的工作日；若间隔≤90天，则连续统计。
• 每位员工有多条记录，包含各项目工作日及前后项目间隔时长。

示例1 #

因前后项目间隔超90天，仅需统计237天有效工作日。

示例2 #

前后项目间隔≤90天，需统计两条记录的工作日总和（518+744=1262）。

示例3 #

因存在超90天的间隔，需统计最后4条记录的工作日总和（183+123+97+518=921天）。

现有SQL问题 #

尝试的SQL语句如下，但针对示例1得到TOTAL_DAYS_WORKED = 3421，不符合预期：

WITH CTE AS (
    SELECT 
        Employee_Code ,
        Employee_Name ,
        START_DATE ,
        END_DATE,
        DAYS_BETWEEN_START_END,
        DAYS_WORKED,
        LAG(END_DATE) OVER (PARTITION BY Employee_Code ORDER BY START_DATE ) AS PREVIOUS_END_DATE,
        CASE 
            WHEN LAG(END_DATE) OVER (PARTITION BY Employee_Code ORDER BY START_DATE) IS NULL THEN 0 -- First row
            WHEN DATEDIFF(DAY, LAG(END_DATE) OVER (PARTITION BY Employee_Code ORDER BY START_DATE), START_DATE) > 90 THEN 1 -- If gap > 90 days
            ELSE 0
        END AS Gap_Exceeds_90_Days
    FROM 
        #TEMP
)
SELECT 
    Employee_Code ,
    Employee_Name ,
    SUM(CASE WHEN Gap_Exceeds_90_Days = 1 THEN 0 ELSE DAYS_WORKED END) AS TOTAL_DAYS_WORKED
FROM 
    CTE
GROUP BY 
    Employee_Code , Employee_Name ;

原SQL错误原因：仅将间隔超90天的单条记录工作日置为0，但未排除该间隔之前的所有记录，不符合“间隔超90天则此前所有工作日不计入”的规则。

修正后的SQL方案 #

WITH CTE AS (
    SELECT 
        Employee_Code,
        Employee_Name,
        START_DATE,
        END_DATE,
        DAYS_WORKED,
        -- 标记当前记录是否为超90天间隔的起始点
        CASE 
            WHEN LAG(END_DATE) OVER (PARTITION BY Employee_Code ORDER BY START_DATE) IS NULL THEN 0
            WHEN DATEDIFF(DAY, LAG(END_DATE) OVER (PARTITION BY Employee_Code ORDER BY START_DATE), START_DATE) > 90 THEN 1
            ELSE 0
        END AS Gap_Exceeds_90_Days
    FROM #TEMP
),
GroupedCTE AS (
    SELECT 
        *,
        -- 累计生成分组ID，每次出现超90天间隔则新建分组
        SUM(Gap_Exceeds_90_Days) OVER (PARTITION BY Employee_Code ORDER BY START_DATE) AS Group_ID
    FROM CTE
)
SELECT 
    Employee_Code,
    Employee_Name,
    SUM(DAYS_WORKED) AS TOTAL_DAYS_WORKED
FROM GroupedCTE
-- 仅统计每个员工最后一个分组的所有记录
WHERE Group_ID = (SELECT MAX(Group_ID) FROM GroupedCTE gc WHERE gc.Employee_Code = GroupedCTE.Employee_Code)
GROUP BY Employee_Code, Employee_Name;

逻辑说明 #

1. 第一阶段CTE标记每条记录是否为超90天间隔的起始记录；
2. GroupedCTE通过累计求和Gap_Exceeds_90_Days生成分组ID：第一次出现超90天间隔时，分组ID递增，后续记录均属于该分组；若再次出现超90天间隔，分组ID再次递增；
3. 最后筛选每个员工的最大分组ID对应的所有记录求和，确保仅统计最后一次超90天间隔后的所有有效工作日，完全符合规则要求。

测试验证：

• 示例1仅统计第三条记录的237天；
• 示例2无超90天间隔，分组ID始终为0，求和1262天；
• 示例3仅统计最后4条记录，求和921天。

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