如何从单个Report Date列提取项目团队的起止日期（含间隔处理）

核心问题分析 #

你的场景核心是识别项目在团队间的分配周期断点：当项目从A团队转到B团队，再转回A团队时，第二次分配给A团队属于新周期，需要重新计算起止日期。而你现有的SQL仅按ProjectID+TeamID分组，无法区分同一团队的多次分配周期，且错误过滤了TeamDays=1的行，导致丢失间隔数据的处理逻辑。

解决方案思路 #

通过窗口函数标记周期分组，步骤如下：

1. 按ProjectID和ReportDate排序，用LAG函数获取上一条记录的TeamID
2. 对比当前行与上一行的TeamID，生成周期分组标识（当TeamID变化时，分组ID递增）
3. 按ProjectID+TeamID+周期分组ID聚合，计算每个周期的StartDate和EndDate

完整SQL代码 #

WITH project_team_periods AS (
    SELECT
        ReportDate,
        ProjectID,
        TeamID,
        TeamDays,
        -- 标记当前行与上一行TeamID是否变化，生成分组标识
        SUM(CASE 
            WHEN LAG(TeamID) OVER(PARTITION BY ProjectID ORDER BY ReportDate) = TeamID THEN 0 
            ELSE 1 
        END) OVER(PARTITION BY ProjectID ORDER BY ReportDate) AS period_group_id
    FROM TEAM_PROJECTS
),
period_aggregates AS (
    SELECT
        ProjectID,
        TeamID,
        period_group_id,
        -- 每个周期的最小ReportDate用于计算StartDate
        MIN(ReportDate) AS min_report_date,
        -- 每个周期的最大ReportDate就是该周期的EndDate
        MAX(ReportDate) AS EndDate
    FROM project_team_periods
    GROUP BY ProjectID, TeamID, period_group_id
)
SELECT
    ProjectID AS 'Project ID',
    TeamID AS 'Team ID',
    -- 用你已掌握的逻辑计算StartDate：ReportDate减去TeamDays再加1天
    CAST(DATEADD(day, -MAX(TeamDays), DATEADD(day, 1, CAST(min_report_date AS DATE))) AS DATE) AS 'Start Date',
    EndDate AS 'End Date'
FROM period_aggregates
JOIN project_team_periods 
    ON period_aggregates.ProjectID = project_team_periods.ProjectID
    AND period_aggregates.TeamID = project_team_periods.TeamID
    AND period_aggregates.period_group_id = project_team_periods.period_group_id
GROUP BY ProjectID, TeamID, period_group_id, min_report_date, EndDate
ORDER BY ProjectID, StartDate;

代码说明 #

• LAG(TeamID) OVER(...)：获取同一项目上一条记录的团队ID，用于判断是否进入新周期
• SUM(CASE...) OVER(...)：生成周期分组ID，每当团队ID变化时，分组ID加1，确保同一团队的多次分配被拆分为不同周期
• 聚合阶段取每个周期的最大ReportDate作为EndDate，结合你已有的StartDate计算逻辑，得到完整的周期起止

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