SQL，加入工作日期到生效日期的工资率

Question

我有 2 个表，一个包含员工工作日期，另一个包含每位员工的工资率以及这些费率何时生效。

它们看起来像这样：

CREATE TABLE pay_rates 
(
    [EMP ID] [int] NOT NULL,
    [RATE] [decimal](11, 6) NOT NULL,
    [EFFECTIVE DATE] [datetime] NOT NULL
)
CREATE TABLE work_dates
(
    [EMP ID] [int] NOT NULL,
    [WORK DATE] [datetime] NOT NULL,
)

pay_rates表中将包含每个员工的许多条目，因为他们的工资多年来一直在调整。

work_dates表包含每个员工的工作日期。

这两个表都包含其他信息，但我已简化为相关列。

我想加入两者，这样我就可以看到每个工作日期的工资率。从技术上讲，我想将每条记录work_dates加入pay_rates中的记录，其中最大EFFECTIVE DATE小于WORK DATE，并且EMP ID匹配。

Answer 1

也许当您加入pay_rates work_dates时，您可以使用not exists来要求不存在生效日期在工作日期或之前并且严格晚于您要加入的费率生效日期的费率。像这样：

declare @pay_rates table (
    [EMP ID] [int] NOT NULL,
    [RATE] [decimal](11, 6) NOT NULL,
    [EFFECTIVE DATE] [datetime] NOT NULL
);
declare @work_dates table (
    [EMP ID] [int] NOT NULL,
    [WORK DATE] [datetime] NOT NULL
);
insert @pay_rates values
    (1, 10.25, '20160615'), 
    (1, 10.65, '20170101'), 
    (1, 11.85, '20180101');
insert @work_dates values 
    (1, '20160701'), 
    (1, '20170101'), 
    (1, '20170701'), 
    (1, '20171231'), 
    (1, '20180102');
select
    WorkDate.[EMP ID],
    WorkDate.[WORK DATE],
    Rate.RATE,
    Rate.[EFFECTIVE DATE]
from
    @work_dates WorkDate
    inner join @pay_rates Rate on
        WorkDate.[EMP ID] = Rate.[EMP ID] and
        WorkDate.[WORK DATE] >= Rate.[EFFECTIVE DATE] and
        not exists
        (
            select 1 
            from @pay_rates LaterRate 
            where
                LaterRate.[EMP ID] = WorkDate.[EMP ID] and
                LaterRate.[EFFECTIVE DATE] <= WorkDate.[WORK DATE] and
                LaterRate.[EFFECTIVE DATE] > Rate.[EFFECTIVE DATE]
        );

我在这里假设您不允许同一名员工在同一天生效两种工资率。在这种情况下，上述查询将为使用这些费率的每个工作日期提供两个结果，因为它无法将其中一个结果优先于另一个。

Answer 2

我认为你需要：

SELECT pr2.[emp id], pr2.rate
FROM pay_rates pr2
INNER JOIN
(SELECT wd.[emp id], MAX(pr.[effective date]) AS mx_date
 FROM work_dates wd
 LEFT JOIN pay_rates pr
 ON (wd.[emp id] = pr.[emp id] AND wd.[work date] > pr.[effective date])
 GROUP BY wd.[emp id]) sub
ON pr2.[emp id] = sub.[emp id] AND pr2.[effective date] = sub.mx_date

在子查询中，选择小于员工工作日期的每个 emp ID 的最大有效天数。然后将结果重新连接到工资率表以拉入工资率。