1

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

Bitemporal table

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.

Alec Bryte
  • 580
  • 1
  • 6
  • 18
  • Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Aug 28 '20 at 06:53
  • Added tags. using postgreSQL and in some cases MS SQL. – Alec Bryte Aug 28 '20 at 10:49
  • At the moment it looks like your `To` dates are *equal* to the `From`s of the 'next' record. You should decide what your granularity is (probably a day makes sense, rather than anything smaller) then make each `From` is the day *after* the preceding `To`. – AakashM Aug 28 '20 at 13:00
  • The "From" is inclusive (>=) while the "To" is exclusive (<). That's why the To of the previous record equals the From of the next one, and therefore there is no overlap. All From/To fields are datetime, and the granularity is millisecond. – Alec Bryte Aug 28 '20 at 14:04
  • In Postgres I would use a daterange: `where daterange(effective_from, effective_to, '[)') @> date '2016-01-02';` –  Aug 28 '20 at 14:45

1 Answers1

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:

bi-temporal

Alec Bryte
  • 580
  • 1
  • 6
  • 18