Bi-temporal SQL table querying

Question

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 1

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: