根据历史记录表创建日期范围报告

Question

我们一直在跟踪历史记录表中的一些更改，如下所示：

ChangeID        EmployeeID          PropertyName        OldValue        NewValue    ModifiedDate
100                 10              EmploymentStart     Not Set         1           2013-01-01
101                 10              SalaryValue         Not Set         55000       2013-01-01
102                 10              SalaryValue         55000           61500       2013-03-20
103                 10              SalaryEffectiveDate 2013-01-01      2013-04-01  2013-03-20
104                 11              EmploymentStart     Not Set         1           2013-01-21
105                 11              SalaryValue         Not Set         43000       2013-01-21
106                 10              SalaryValue         61500           72500       2013-09-20
107                 10              SalaryEffectiveDate 2013-04-01      2013-10-01  2013-09-20

基本上，如果员工的工资发生变化，我们会在历史记录表中记录两行。一行用于工资值本身，另一行用于工资生效日期。因此，这两者具有相同的修改日期/时间，并且可以安全地假设它们在数据库中始终相互跟随。我们还可以假设工资值总是首先记录(因此它是相应生效日期之前的一条记录

现在，我们正在研究根据给定的日期范围将报告创建到如下所示的表中：

Annual Salary Change Report (2013)
EmployeeID      Date1           Date2           Salary
10              2013-01-01      2013-04-01      55000
10              2013-04-01      2013-10-01      61500
10              2013-10-01      2013-12-31      72500
11              2013-03-21      2013-12-31      43000

我过去曾通过将表连接到自身来做类似的事情，但在这些情况下，生效日期和新值在同一行中。现在，我必须通过查看现有历史记录表的几行来创建输出表的每一行。有没有一种使用游标的简单方法？

编辑#1：

我正在阅读此内容，显然使用 PIVOT 是可行的

提前非常感谢你。

Answer 1

您可以使用自联接来获得所需的结果。诀窍是创建一个 cte 并为每个 EmployeeID 添加两行，如下所示(我称历史表 ht)：

with cte1 as
(
select EmployeeID, PropertyName, OldValue, NewValue, ModifiedDate
from ht
union all
select t1.EmployeeID,
(case when t1.PropertyName = "EmploymentStart" then "SalaryEffectiveDate"         else t1.PropertyName end),
(case when t1.PropertyName = "EmploymentStart" then t1.ModifiedDate else t1.NewValue end),
(case when t1.PropertyName = "SalaryValue" then t1.NewValue
when t1.PropertyName = "SalaryEffectiveDate" then "2013-12-31"
when t1.PropertyName = "EmploymentStart" then "2013-12-31" end),
"2013-12-31"
from ht t1
where t1.ModifiedDate = (select max(t2.ModifiedDate) from ht t2 where t1.EmployeeID = t2.EmployeeID)
)
select t3.EmployeeID, t4.OldValue Date1, t4.NewValue Date2, t3.OldValue Salary
from cte1 t3
inner join cte1 t4 on t3.EmployeeID = t4.EmployeeID
and t3.ModifiedDate = t4.ModifiedDate
where t3.PropertyName = "SalaryValue"
and t4.PropertyName = "SalaryEffectiveDate"
order by t3.EmployeeID, Date1

我希望这有所帮助。

Answer 2

使用 pivot 有点过分了，因为你只需要两个属性。使用 GROUP BY 也可以实现这一点：

;WITH cte_salary_history(EmployeeID,SalaryEffectiveDate,SalaryValue)
AS
(
SELECT EmployeeID,
MAX(CASE WHEN PropertyName='SalaryEffectiveDate' THEN NewValue ELSE NULL END) AS SalaryEffectiveDate,
MAX(CASE WHEN PropertyName='SalaryValue' THEN NewValue ELSE NULL END) AS SalaryValue
FROM yourtable
GROUP BY EmployeeID,ModifiedDate
)
SELECT EmployeeID,SalaryEffectiveDate,
LEAD(SalaryEffectiveDate,1,'9999-12-31') OVER(PARTITION BY EmployeeID ORDER BY SalaryEffectiveDate)  AS SalaryEndDate,
SalaryValue
FROM cte_salary_history