SQL Server 2012 - 检索日期介于两个日期之间的值

Question

>我有一个名为"费率"的表：

Valid From     Employee      Rate
----------------------------------
01/03/2010     1M            50
01/03/2010     2M            75
01/10/2015     1M            55
01/10/2015     2M            80

我还有以下名为"工作"的表：

ID        Employee         OpenedDate     Rate
100000    1M               05/06/2012
100000    2M               08/09/2018

如何将费率从"费率"表检索到"作业"表中，其中"打开日期"大于或等于当前"有效开始日期"，小于或等于"员工"也匹配的下一个"有效开始日期"？

所以我最终会得到：

ID        Employee         OpenedDate     Rate
100000    1M               05/06/2012     50
100000    2M               08/09/2018     80

希望我解释清楚 为任何和所有的帮助干杯！

ps 不确定如何在 Stack 中将上述数据显示为表格布局，一直在查看帮助，但我看不到如何？？

Answer 1

如果您无法修改表以添加"ValidTo"列，则必须使用LEAD窗口函数动态创建一个

CREATE TABLE Table1
([Valid From] DATETIME, Employee varchar(2), Rate int)
;
INSERT INTO Table1
([Valid From], Employee, Rate)
VALUES
('2010-01-03 00:00:00', '1M', 50),
('2010-01-03 00:00:00', '2M', 75),
('2015-01-10 00:00:00', '1M', 55),
('2015-01-10 00:00:00', '2M', 80)
;
CREATE TABLE Table2
(ID int, Employee varchar(2), OpenedDate DATETIME, Rate int)
;
INSERT INTO Table2
(ID, Employee, OpenedDate, Rate)
VALUES
(100000, '1M', '2012-05-06 00:00:00', NULL),
(100000, '2M', '2018-08-09 00:00:00', NULL)
;
;WITH cteValidToAdded
AS(
SELECT
T1.[Valid From]
,[ValidTo] = ISNULL(LEAD(T1.[Valid From])OVER(PARTITION BY T1.Employee ORDER BY T1.[Valid From], T1.Employee),'25001212') --Some date in distance future
,T1.Employee
,T1.Rate 
FROM dbo.Table1 T1
)
SELECT 
T2.ID
,T2.Employee
,OpenedDate = CONVERT(VARCHAR(12), T2.OpenedDate, 101)
,V.Rate
FROM dbo.Table2 T2
LEFT JOIN cteValidToAdded V ON V.Employee = T2.Employee 
AND T2.OpenedDate >= V.[Valid From] AND T2.OpenedDate < V.ValidTo

输出

ID      Employee    OpenedDate  Rate
100000  1M          05/06/2012  50
100000  2M          08/09/2018  80

Answer 2

只需使用outer apply：

select j.*, r.rate
from jobs j outer apply
(select top (1) r.*
from rates r
where r.employee = j.employee and
r.valid_from <= j.opened_date
order by r.valid_from desc
) r;

在rates(employee, valid_from)上索引(可能包括rate(，这应该比使用窗口函数的版本更快。