修正SQL查询：从在职员工列表提取新员工原部门避免重复统计

需求与问题说明 #

需要从在职员工列表按部门提取新员工数据，现有查询存在以下问题：

• 使用EmployeeID条件时能得到正确的原部门；
• 使用DepartmentName条件时，员工3会同时被统计为Finance和Marketing部门的新员工，但Marketing是该员工转岗后的新部门，实际应仅在Finance部门显示为新员工。

需要修改查询实现该效果，或新增原部门名称列。

数据示例 #

EmployeeID       Department    OrganizationStartDate       RehireDate    AsOfDate
1                Finance       10/15/2021                  10/15/2021    1/31/2022
2                Finance       11/15/2021                  11/15/2021    1/31/2022
1                Finance       10/15/2021                  10/15/2021    2/28/2022
2                Finance       11/15/2021                  11/15/2021    2/28/2022
3                Finance       2/13/2022                   2/13/2022     2/28/2022
1                Finance       10/15/2021                  10/15/2021    3/31/2022
2                Finance       11/15/2021                  11/15/2021    3/31/2022
3                Finance       2/13/2022                   2/13/2022     3/31/2022
3                Marketing     2/13/2022                   2/13/2022     4/30/2022

原查询SQL #

select 
    Year
    ,Month
    ,HireDate
    ,EmployeeID
    ,DepartmentName
    ,count(EmployeeID) as TotalNumberOfNewHires
from --HireTimes
(
    SELECT 
        year(isnull(RehireDate, OrganizationStartDate)) as Year
        , month(isnull(RehireDate, OrganizationStartDate)) as Month
        , isnull(RehireDate, OrganizationStartDate) as HireDate
        , EmployeeID
        ,DepartmentName
        , ROW_NUMBER() over(partition by  EmployeeID, isnull(RehireDate, OrganizationStartDate)
           order by RehireDate, OrganizationStartDate  DESC) AS RID
    FROM   [Employees]
    WHERE isnull(RehireDate, OrganizationStartDate) >  '2022-01-01' 
        --and DepartmentName = 'Finance' -- 'Marketing'
        --and EmployeeID = 3
 ) HireTimes 
where RID = 1
group by 
    year
    ,month
    ,HireDate
    ,EmployeeID
    ,DepartmentName

解决方案 #

方案1：仅保留员工入职时的原部门统计

修改查询逻辑，取每个员工入职/重新入职时间对应的最早部门（即入职时的原部门），避免转岗后的部门被误统计：

SELECT 
    Year
    , Month
    , HireDate
    , EmployeeID
    , OriginalDepartment AS DepartmentName
    , COUNT(EmployeeID) AS TotalNumberOfNewHires
FROM (
    SELECT 
        YEAR(ISNULL(RehireDate, OrganizationStartDate)) AS Year
        , MONTH(ISNULL(RehireDate, OrganizationStartDate)) AS Month
        , ISNULL(RehireDate, OrganizationStartDate) AS HireDate
        , EmployeeID
        , Department AS OriginalDepartment
        , ROW_NUMBER() OVER (
            PARTITION BY EmployeeID, ISNULL(RehireDate, OrganizationStartDate)
            ORDER BY AsOfDate ASC -- 按日期升序取最早的部门（入职时部门）
        ) AS RID
    FROM [Employees]
    WHERE ISNULL(RehireDate, OrganizationStartDate) > '2022-01-01'
) HireTimes
WHERE RID = 1
GROUP BY 
    Year
    , Month
    , HireDate
    , EmployeeID
    , OriginalDepartment

方案2：新增原部门列，同时保留当前部门

如果需要同时查看员工当前部门和入职原部门，可以新增OriginalDepartment列，明确区分：

SELECT 
    Year
    , Month
    , HireDate
    , EmployeeID
    , CurrentDepartment
    , OriginalDepartment
    , COUNT(EmployeeID) AS TotalNumberOfNewHires
FROM (
    SELECT 
        YEAR(ISNULL(RehireDate, OrganizationStartDate)) AS Year
        , MONTH(ISNULL(RehireDate, OrganizationStartDate)) AS Month
        , ISNULL(RehireDate, OrganizationStartDate) AS HireDate
        , EmployeeID
        , Department AS CurrentDepartment
        -- 提取入职周期内的最早部门作为原部门
        , FIRST_VALUE(Department) OVER (
            PARTITION BY EmployeeID, ISNULL(RehireDate, OrganizationStartDate)
            ORDER BY AsOfDate ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS OriginalDepartment
        , ROW_NUMBER() OVER (
            PARTITION BY EmployeeID, ISNULL(RehireDate, OrganizationStartDate)
            ORDER BY AsOfDate DESC -- 取最新记录作为当前部门
        ) AS RID
    FROM [Employees]
    WHERE ISNULL(RehireDate, OrganizationStartDate) > '2022-01-01'
) HireTimes
WHERE RID = 1
GROUP BY 
    Year
    , Month
    , HireDate
    , EmployeeID
    , CurrentDepartment
    , OriginalDepartment

效果说明 #

两种方案都能确保员工3仅在Finance部门被统计为新员工，不会出现在Marketing的新员工统计结果中。

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