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
(
SELECT
e.EmployeeId,
d.EmployeeDataId,
d.EffectiveDate,
d.EmployeeNumber,
d.EmploymentType,
d.HireDate,
etc...,
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
WHERE
e.EmployeeId = @EmployeeId
AND (d.EffectiveDate <= @EffectiveDate)
)

Next, we pull the result out of the previous query, grabbing only the top row number:

SELECT
EmployeeId,
EmployeeDataId,
EffectiveDate,
EmployeeNumber,
EmploymentType,
HireDate,
etc...
FROM ranked
WHERE
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.

Advertisements

About Ed Jones

Ed is a Connected Systems and .NET Specialist for RBA in the Twin Cities. Contact Ed

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: