create date range report based on history table

Question

We have been keeping track of some changes in a History Table like this:

    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

Basically if an Employee's Salary changes, we log two rows in the history table. One row for the Salary value itself and the other row for the salary effective date. So these two have identical Modification Date/Time and are kind safe to assume that are always after each other in the database. We can also assume that Salary Value is always logged first (so it is one record before the corresponding effective date

Now we are looking into creating reports based on a given date range into a table like this:

    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

I have done something similar in the past by joining the table to itself but in those cases the effective date and the new value where in the same row. Now I have to create each row of the output table by looking into a few rows of the existing history table. Is there an straightforward way of doing this whitout using cursors?

Edit #1:

Im reading on this and apparently its doable using PIVOTs

Thank you very much in advance.

Answer 1

You can use self join to get the result you want. The trick is to create a cte and add two rows for each EmployeeID as follows (I call the history table 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

I hope this helps.

Answer 2

It is a little over kill to use pivot since you only need two properties. Use GROUP BY can also achieve this:

;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