基于月度快照计算各部门每周招聘、复聘与离职统计

我们有一张Employee_History表，存储每位员工在离职当月之前每月月末的快照数据，同时每日更新当前员工的当日快照直至月末。

我们需要按部门统计每周的Hires（招聘）、Rehires（复聘）与Terms（离职）数据，但由于仅采集月度数据而非周度数据，拆分周度统计时易出现重复数据。

目前已能通过如下SQL获取月度统计数据，请问如何基于仅有的月度条目实现按每月内的周分组统计？

select 
    Max(AsOfDate) as AsOfDate,
    Sector,
    Department,

    sum(case
        when DatePart(Year, TermDate) = DatePart(Year, AsOfDate) and DatePart(Month, TermDate) = DatePart(Month, AsOfDate) then 1
        else 0
    end) as Terms,

    sum(case
        when DatePart(Year, HireDate) = DatePart(Year, AsOfDate) and DatePart(Month, HireDate) = DatePart(Month, AsOfDate) then 1
        else 0
    end) as Hires,

    sum(case
        when DatePart(Year, RehireDate) = DatePart(Year, AsOfDate) and DatePart(Month, RehireDate) = DatePart(Month, AsOfDate) then 1
        else 0
    end) as Rehires

from Employee_History
group by Year(AsOfDate), datepart(Month, AsOfDate), Department

假设当前日期为2022-03-17，示例数据如下：

以2022年1月为例，期望输出如下：

要实现月度数据拆分为周度统计，核心思路是生成月度周序列、去重员工异动数据、匹配异动所属周、最后按部门和周聚合，同时补全无异动的部门周记录。以下是SQL Server环境下的具体实现：

-- 生成目标月份的周起始日期序列（示例为2022年1月，可扩展为动态生成）
WITH WeeklyDates AS (
    SELECT DATEFROMPARTS(2022, 1, 1) AS WeekStart
    UNION ALL
    SELECT DATEADD(week, 1, WeekStart)
    FROM WeeklyDates
    WHERE WeekStart < EOMONTH(DATEFROMPARTS(2022, 1, 1))
),
-- 去重员工异动数据，避免快照重复统计
UniqueEmployeeEvents AS (
    SELECT DISTINCT
        EmployeeID,
        Department,
        HireDate,
        RehireDate,
        TermDate
    FROM Employee_History
),
-- 将员工异动匹配到对应周
EmployeeWeeklyEvents AS (
    SELECT
        w.WeekStart,
        u.Department,
        CASE WHEN u.HireDate BETWEEN w.WeekStart AND DATEADD(day, 6, w.WeekStart) THEN 1 ELSE 0 END AS IsHire,
        CASE WHEN u.RehireDate BETWEEN w.WeekStart AND DATEADD(day, 6, w.WeekStart) THEN 1 ELSE 0 END AS IsRehire,
        CASE WHEN u.TermDate BETWEEN w.WeekStart AND DATEADD(day, 6, w.WeekStart) THEN 1 ELSE 0 END AS IsTerm
    FROM WeeklyDates w
    CROSS JOIN UniqueEmployeeEvents u
    WHERE (
        (u.HireDate IS NOT NULL AND YEAR(u.HireDate)=YEAR(w.WeekStart) AND MONTH(u.HireDate)=MONTH(w.WeekStart))
        OR (u.RehireDate IS NOT NULL AND YEAR(u.RehireDate)=YEAR(w.WeekStart) AND MONTH(u.RehireDate)=MONTH(w.WeekStart))
        OR (u.TermDate IS NOT NULL AND YEAR(u.TermDate)=YEAR(w.WeekStart) AND MONTH(u.TermDate)=MONTH(w.WeekStart))
    )
)
-- 按部门和周聚合，同时补全所有部门的周记录
SELECT
    AllDeptWeeks.WeekStart AS AsOfDate,
    AllDeptWeeks.Department,
    ISNULL(SUM(IsHire), 0) AS Hires,
    ISNULL(SUM(IsRehire), 0) AS Rehires,
    ISNULL(SUM(IsTerm), 0) AS Terms
FROM (
    SELECT w.WeekStart, d.Department
    FROM WeeklyDates w
    CROSS JOIN (SELECT DISTINCT Department FROM UniqueEmployeeEvents) d
) AllDeptWeeks
LEFT JOIN EmployeeWeeklyEvents e
    ON AllDeptWeeks.WeekStart = e.WeekStart AND AllDeptWeeks.Department = e.Department
GROUP BY AllDeptWeeks.WeekStart, AllDeptWeeks.Department
ORDER BY AllDeptWeeks.Department, AllDeptWeeks.WeekStart;

关键说明 #

• 周序列生成：用递归CTE生成指定月份的所有周起始日期，确保覆盖整个月份；
• 去重处理：通过DISTINCT获取唯一的员工异动数据，解决快照重复导致的统计错误；
• 异动匹配：通过日期范围判断，将每个异动事件映射到对应的周；
• 补全空记录：通过部门与周的笛卡尔积做左连接，保证每个部门的每周都有统计结果（即使无异动）。

若需支持动态月份范围，可修改WeeklyDates的起始条件，比如基于Employee_History中最小和最大AsOfDate自动生成时间序列。

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