Temporal Data in a Relational Database Pt. 2
The post that I wrote a few years ago regarding working with temporal data has gotten a few more reads than I’d expected, and since I’ve implemented the pattern successfully a few more times since then, I thought I’d update the article with the addition of another way to approach the SQL code.
The basics of the pattern remain the same, so I simply refer you to the article, Temporal Data in a Relational Database, for a description.
Start by retrieving the results that meet your effective date criteria and create a Common Table Expression (CTE) to represent a ranked list that orders the results descending by effective date. Here, we partition by the employee id so ROW_NUMBER returns a ranking for each employee.
WITH ranked AS
ROW_NUMBER() OVER (PARTITION BY e.EmployeeId
ORDER BY d.EffectiveDateTime DESC) AS RowNumber
FROM Employee e
INNER JOIN EmployeeData d ON e.EmployeeId = d.EmployeeId
e.EmployeeId = @EmployeeId
AND (d.EffectiveDate <= @EffectiveDate)
Next, we pull the result out of the previous query, grabbing only the top row number:
RowNumber = 1
This query is a lot easier to read and make sense of. However, it does add the additional overhead of figuring out how many rows are in a CTE before pulling out the ones that are needed. As such, you may not want to use this approach if the record count is particularly high.