SCD2表指定时段员工总数SQL查询方案咨询

问题背景 #

需要获取SCD2类型员工表中指定时段内的员工总数，之前使用FlagIsCurrent字段查询结果不一致，原因是该字段仅反映当前状态，无法准确统计历史时段数据。已知表中有ValidFrom（生效起始日期）和ValidTo（生效结束日期）字段，对应员工在门店的任职/异动时段，希望通过这两个字段实现准确统计。

表结构 #

现有可用查询 #

指定时段入职人数查询 #

-- 关联日历表查询时段范围
SELECT COUNT(DISTINCT [EmployeeID])
FROM [import].[hr].[Employees]
WHERE [EmploymentDate] >= MIN(Calendar[Date]) 
  AND [EmploymentDate] <= MAX(Calendar[Date])

-- 或指定具体月份
SELECT COUNT(DISTINCT [EmployeeID])
FROM [import].[hr].[Employees]
WHERE [EmploymentDate] >= '2023-02-01' 
  AND [EmploymentDate] <= '2023-02-28'

指定时段离职人数查询 #

-- 关联日历表查询时段范围
SELECT COUNT(DISTINCT [EmployeeID])
FROM [import].[hr].[Employees]
WHERE [TerminationDate] <> '1900-01-01' 
  AND [TerminationDate] >= MIN(Calendar[Date]) 
  AND [TerminationDate] <= MAX(Calendar[Date])

-- 或指定具体月份
SELECT COUNT(DISTINCT [EmployeeID])
FROM [import].[hr].[Employees]
WHERE [TerminationDate] <> '1900-01-01' 
  AND [TerminationDate] >= '2023-02-01' 
  AND [TerminationDate] <= '2023-02-28'

问题查询（结果不一致） #

使用FlagIsCurrent字段的查询仅能统计当前状态员工，无法覆盖历史时段的异动数据，导致结果不准：

-- 关联日历表查询时段范围
SELECT COUNT(DISTINCT [EmployeeID])
FROM [import].[hr].[Employees]
WHERE [FlagIsCurrent] = '1'
  AND (
    ([TerminationDate] = '1900-01-01' AND [EmploymentDate] <= MAX(Calendar[Date]))
    OR 
    ([EmploymentDate] <= MAX(Calendar[Date]) AND [TerminationDate] >= MIN(Calendar[Date]))
  )

-- 或指定具体月份
SELECT COUNT(DISTINCT [EmployeeID])
FROM [import].[hr].[Employees]
WHERE [FlagIsCurrent] = '1'
  AND (
    ([TerminationDate] = '1900-01-01' AND [EmploymentDate] <= '2023-02-28')
    OR 
    ([EmploymentDate] <= '2023-02-28' AND [TerminationDate] >= '2023-02-01')
  )

更新疑问 #

是否可以通过以下SQL获取指定时段的员工总数？

-- 关联日历表查询时段范围
SELECT COUNT([EmployeeID]) 
FROM [import].[hr].[Employees] 
WHERE [TerminationDate] IS '1900-01-01' 
  AND [ValidFrom] <= MAX(Calendar[Date]) 
  AND [ValidTo] >= MIN(Calendar[Date])

-- 或针对2023年2月
SELECT COUNT([EmployeeID]) 
FROM [import].[hr].[Employees] 
WHERE [TerminationDate] IS '1900-01-01' 
  AND [ValidFrom] <= '2023-02-28' 
  AND [ValidTo] >= '2023-02-01'

正确解决方案 #

核心思路 #

SCD2表的ValidFrom和ValidTo是统计历史时段数据的关键，判断逻辑是：员工的生效时段与查询时段存在交集，即员工在查询时段内至少有一天处于任职/有效状态。需要注意：

1. 不能过滤TerminationDate = '1900-01-01'，否则会排除已离职但在查询时段内有任职记录的员工
2. 必须用DISTINCT去重，因为SCD2表中同一员工可能有多条异动记录

通用SQL模板 #

方式1：关联日历表查询时段范围

SELECT COUNT(DISTINCT [EmployeeID]) AS 员工总数
FROM [import].[hr].[Employees]
WHERE 
  -- 员工生效时段与查询时段存在交集
  [ValidFrom] <= MAX(Calendar.[Date]) 
  AND [ValidTo] >= MIN(Calendar.[Date])

方式2：指定具体时段（如2023年2月）

SELECT COUNT(DISTINCT [EmployeeID]) AS 员工总数
FROM [import].[hr].[Employees]
WHERE 
  [ValidFrom] <= '2023-02-28' 
  AND [ValidTo] >= '2023-02-01'

针对疑问的修正说明 #

你提供的更新疑问中的SQL存在两个问题：

1. 错误使用IS '1900-01-01'，SQL中判断字符串相等应该用=而非IS（IS用于判断NULL）
2. 过滤了TerminationDate = '1900-01-01'，会排除已离职但在查询时段内有任职记录的员工，导致统计不全

修正后的SQL（以2023年2月为例）：

SELECT COUNT(DISTINCT [EmployeeID]) AS 员工总数
FROM [import].[hr].[Employees]
WHERE 
  [ValidFrom] <= '2023-02-28' 
  AND [ValidTo] >= '2023-02-01'

验证示例 #

以2023年2月为例，统计结果应该包含：

• e01：生效时段完全覆盖2023年2月
• e02：生效时段覆盖2023年2月
• e03：生效时段与2023年2月有交集
• e04：已离职且2023年2月不在职，不统计

最终统计总数为3，符合预期。

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