根据每月快照计算每周雇用、重新雇用和终止

Question

我们有一个表，其中包含每个员工在每个月底直到他们离开公司的那个月的数据快照。此表还包含每个员工当天的快照，该快照每天替换一次，直到月末。

我们要做的是为每个部门选择每周雇佣、再雇佣和期限的统计数据。但是，由于我们只按月而不是按周捕获数据，所以我很难按周进行分解，而不会得到重复的数据。

我可以拉出类似的每月统计数据。如果一个月只有一个条目，是否有一种方法可以按每个星期分组?

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的示例数据

TitleRehireDatetbody> <<tr>

AsOfDate	EmployeeID	Department	HireDate	TermDate
2022-01-31	EMP22	人力资源	管理	2021-01-12	空	2022-01-17
2022-01-31	EMP45	,	程序员	2022-01-10	空	空
2022-02-28	EMP45	,	程序员	2022-01-10	空	空
2022-03-17	EMP45	,	程序员	2022-01-10	空	空
2022-01-31	EMP03	,	经理	2018-08-17	2022-01-24	空
2022-02-28	EMP03	,	经理	2018-08-17	2022-01-24	空
2022-03-17	EMP03	,	经理	2018-08-17	2022-01-24	空

您需要的是一周的映射表<->包含:

的月底内容

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

一个问题是你的快照表包含"当前日期"。如果这个月还没有结束。我建议改变这一点，这样就可以在月底简化工作。或者，为它创建一个新列。

用你的周的逻辑填充它，一些使用ISO WEEK，一些使用从新年开始的一天等等。

然后将快照连接到该表(并且需要处理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

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