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

如何将 Rates 表中的费率检索到 Jobs 表中，其中 OpenedDate 大于或等于当前 ValidFrom 日期并且小于或等于下一个 ValidFrom 日期，其中 Employee 也匹配？

所以我最终会得到：

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

希望我能解释一下，为任何和所有帮助干杯！

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

标签： sqlsql-serversql-server-2012

Answer 1

如果您无法修改表格以添加“ValidTo”列，那么您必须使用LEADWindow 函数动态创建一个

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