Bi-temporal SQL table querying

I’m trying to model a SQL table to store salary changes for employees. One approach is using a bi-temporal table as follows:

Here an employee was hired on 10/1/2015 with salary of 100,000. Then on 2/15/2016 he had a performance review and his boss said, “we’re bumping your salary to 110,000 effective start of current year (1/1/2016).

To store this information, I’m using two sets of date ranges. The “effective” tells you when the salary was valid, e.g. $100k until 1/1/2016 and $110k from then on. On the other hand, the “settled” range indicates when the decision was made, in this case 2/15/2016. Thus I should be able to query for the following scenarios:

what was his salary in Dec 2015 looking at the moment after salary review = old salary
what was his salary on Jan 2, 2016 before he had the review = old salary (because employee didn’t know about the raise yet)
what was his salary on Jan 2, 2016 after he had the review = new salary (because employee now knows about the raise)
etc

It seems I have two variables: effectiveOn, and settledOn. However, I’m struggling to come up with a SQL query that would produce the right results.

Here’s what I’ve tried so far (does not work for all scenarios):

SELECT *
FROM Employees 
WHERE (EmployeeId = 10)
 AND 
    (
      ((SettledFrom <= @settledOn) AND (SettledTo IS NULL OR (SettledTo > @settledOn)))
      AND ((EffectiveFrom <= @effectiveOn) AND (EffectiveTo IS NULL OR (EffectiveTo > @effectiveOn)))
    )

Ideally I would need a SQL query that works in all scenarios and produces exactly one result row each time. Any help is greatly appreciated. Same for any improvements on the table design.

Answer

After further investigation, I determined that my SQL query was indeed correct. However, for the scenario to be fully bi-temporal, I was missing several rows in the DB. Here’s what the DB should look like:

Advertisement

Answer