sql - 双时态 SQL 表查询

Question

我正在尝试建模一个 SQL 表来存储员工的工资变化。一种方法是使用双时表，如下所示：

在这里，一名员工于 2015 年 10 月 1 日被聘用，工资为 100,000。然后在 2016 年 2 月 15 日，他进行了一次绩效评估，他的老板说：“我们将把你的薪水提高到 110,000，从今年开始（2016 年 1 月 1 日）。

为了存储这些信息，我使用了两组日期范围。“有效”告诉您工资何时有效，例如 2016 年 1 月 1 日之前的 10 万美元和从那时起的 11 万美元。另一方面，“已结算”范围表示做出决定的时间，在本例中为 2016 年 2 月 15 日。因此，我应该能够查询以下场景：

• 他在 2015 年 12 月的薪水是多少，看看薪水审查后的那一刻 = 旧薪水
• 他在 2016 年 1 月 2 日的工资是多少，在他接受审查之前 = 旧工资（因为员工还不知道加薪）
• 审核后他在 2016 年 1 月 2 日的薪水是多少 = 新薪水（因为员工现在知道加薪了）
• ETC

看来我有两个变量：有效的，和解决的。但是，我正在努力想出一个能够产生正确结果的 SQL 查询。

这是我迄今为止尝试过的（不适用于所有场景）：

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)))
    )

理想情况下，我需要一个适用于所有场景并且每次只生成一个结果行的 SQL 查询。任何帮助是极大的赞赏。对表格设计的任何改进也是如此。

标签： sqlsql-serverpostgresqltemporalbi-temporal

Answer 1

经过进一步调查，我确定我的 SQL 查询确实是正确的。但是，对于完全双时态的场景，我在数据库中丢失了几行。数据库应该是这样的：