时间戳

首页 > 解决方案 > 根据当前时间点的部门对员工历史记录表进行不同计数

sql - 根据当前时间点的部门对员工历史记录表进行不同计数

问题描述

所以我有一个员工表，其中包含从一开始就所有员工的数据。在数据中，我拥有我应该需要的所有数据。我有员工的开始日期、结束日期（如果没有，则为 null），我有部门的名称，如果部门已更改，则该特定员工有一个新行，具有新的部门值，以及名为“DepValidFrom”的两列和“DepValidto”，以日期格式确定当前员工在该特定部门的时间段。

我的目标是，进入一个矩阵，将所有部门的列表作为行，以年和月作为列，并将当时该部门的员工人数作为值。我拥有所有数据，只是找不到编写 PowerBI Measure 甚至 SQL 查询的确切方法。

所以....我正试图将其引入 Power BI，但我得到的视图不完整。我希望我的数据如下所示：

Department | Jan | Feb | Mar | Apr |
Dep1       |  3  |  5  |  6  |  4  |
Dep2       |  2  |  3  |  2  |  3  |
Dep3       |  1  |  1  |  2  |  3  |

现在我只是使用一个非常简单DISTINCTCOUNT(Emp_Table[EmployeeInitials])的方法，它给了我一个不完整的视图，因为它只计算特定日期，并且不会将数字保留到总数中，留下一堆空值。

我希望有人能理解我的意思，并且有人可以提供帮助！

谢谢！

标签： sqlsql-serverdatabasecountpowerbi

解决方案

您可以首先取消透视日期并生成一个查询，该查询给出每个部门和日期的员工人数：

select e.dept, x.dt, sum(cnt) over(partition by dept order by dt) cnt
from employees e
cross apply (values (startdate, 1), (enddate, -1)) as x(dt, cnt)
where dt is not null

然后，您可以进行条件聚合以旋转结果 - 这需要枚举日期：

select dept, 
    max(case when dt >= '20200101' and dt < '20200201' then cnt else 0 end) cnt_202001,
    max(case when dt >= '20200201' and dt < '20200301' then cnt else 0 end) cnt_202002,
    ...
from (
    select e.dept, x.dt, sum(cnt) over(partition by dept order by dt) cnt
    from employees e
    cross apply (values (startdate, 1), (enddate, -1)) as x(dt, cnt)
    where dt is not null
) t
group by dept

当员工在月中发生变动时，该月的两个部门都计算在内。

推荐阅读