sql-server - 根据历史表创建日期范围报告

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

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

现在我们正在考虑将给定日期范围的报告创建到如下表格中:

    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:

我正在阅读这篇文章，显然它可以使用 PIVOTs 实现

非常感谢您。

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

希望对您有所帮助。