SQL Server 根据每月快照计算每周聘用、重新聘用和离职

我们有一个表，其中包含每个雇员在每个月底到他们离开公司的那个月的数据快照。该表还包含每个雇员在当天的快照，该快照每天都会被替换，直到月底。
我们尝试为每个部门的聘用、重新聘用和条款选择每周统计数据。但是，由于我们只按月而不是按周捕获数据，因此我很难按周进行细分而不获得重复项。
我可以拉类似于这个月的统计数据。如果一个月只有一个条目，有没有一个方法可以按一个月中的每个星期分组？

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年3月17日的示例数据
| 截止日期|员工ID|部门|职务|聘用日期|重新聘用日期|期限日期|
| - ------| - ------| - ------| - ------| - ------| - ------| - ------|
| 2022年1月31日|EMP22|人力资源|管理员|2021年1月12日|零|2022年1月17日|
| 2022年1月31日|EMP45|信息技术|程控仪|2022年1月10日|零|零|
| 2022年2月28日|EMP45|信息技术|程控仪|2022年1月10日|零|零|
| 2022年3月17日|EMP45|信息技术|程控仪|2022年1月10日|零|零|
| 2022年1月31日|EMP03|信息技术|经理|2018年8月17日|2022年1月24日|零|
| 2022年2月28日|EMP03|信息技术|经理|2018年8月17日|2022年1月24日|零|
| 2022年3月17日|EMP03|信息技术|经理|2018年8月17日|2022年1月24日|零|
例如，2022年1月的预期产出
| 截止日期|部门|雇用|重新雇用|条款|
| - ------| - ------| - ------| - ------| - ------|
| 2022年1月1日|人力资源|无|无|无|
| 2022年1月8日|人力资源|无|无|无|
| 2022年1月15日|人力资源|无|无|无|
| 2022年1月22日|人力资源|无|无|1个|
| 2022年1月29日|人力资源|无|无|无|
| 2022年1月1日|信息技术|无|无|无|
| 2022年1月8日|信息技术|无|无|无|
| 2022年1月15日|信息技术|1个|无|无|
| 2022年1月22日|信息技术|无|无|无|
| 2022年1月29日|信息技术|无|1个|无|

您需要的是一个Map表，用于<->月末的事情，其中包含：

create table weekmap(asOfDate DATE PRIMARY KEY, weekDayStart DATE, weekDayEnd DATE)

一个问题是，如果月份没有结束，快照表中会包含“当前日期”。我建议更改此设置，使其始终包含月末以简化操作。或者，为此创建新列。
填充它与任何逻辑你的周应该是，一些使用ISO周，一些使用从新年开始的一天等。
然后，根据该表连接快照（并且需要处理asOfDate不是月末的情况）：

select w.asOfDate, w.weekDayStart, t.Department
, SUM(case when HireDate between weekdaystart and weekdayend then 1 else 0 end) AS hires
, SUM(case when ReHireDate between weekdaystart and weekdayend then 1 else 0 end) AS rehires
, SUM(case when TermDate between weekdaystart and weekdayend then 1 else 0 end) AS term
from snapshottable t
inner join weekmap w
 ON w.asOfDate = t.asOfDateFixedEndOfMonth
group by w.asOfDate, w.weekDayStart, t.Department

如果一个人在一个月内被雇佣和解雇两次，那么数据会丢失一些，但这样你可能会遇到更大的问题

SQL Server 根据每月快照计算每周聘用、重新聘用和离职

1条答案

相关问题

热门标签

最新问答