日期列和滞后函数之间的日期范围的 SQL 大小写表达式

Question

我正在尝试为报告构建一个数据集，其中我必须从具有值的不同表中检索数据。 我有一个名为"费率"的表，我需要从"费率"列中检索费率值。在此表中，我还有一个"eff_Date"列。

我还有一个"日期">

表，其中有一个"日期"列。

我希望我的条件是这样的，如果"日期"表中日期列中的值介于"Rate"表中的eff_date和end_eff_date之间(使用滞后函数来获取(，然后拉取值率值。

我尝试使用 SQL CASE 表达式来实现这一点，但由于我无法将 Lag 函数添加到 CASE 表达式中，因此无法继续使用它。

Answer 1

这会将您的费率表示为开始日期和结束日期，然后您可以将其与日期进行比较：

CREATE TABLE [dbo].[Rate]( [CompanyNo] [int] NOT NULL, [Eff_date] [datetime] NOT NULL, [rate] [nchar](10) NOT NULL ) ON [PRIMARY]
Insert into dbo.Rate values('05','01-10-2018','245.11'), ('05','01-09-2015','234.79');
CREATE TABLE [dbo].[Date]( [CompanyNo] [int] NOT NULL, [ResidentNo] [int] NOT NULL, [date] [datetime] NOT NULL ) ON [PRIMARY]
Insert into dbo.Date values('05','108276','08-03-2019'), ('05','108276','01-08-2016');
WITH cte as (
SELECT *,LEAD(eff_date) OVER (ORDER BY eff_date) as end_eff_date  FROM rate
)
SELECT * FROM date
INNER JOIN cte
ON cte.Eff_date <= date.date
AND (date.date < cte.end_eff_date OR cte.end_eff_date IS NULL);

Answer 2

试试这个。

SELECT a.* FROM 
(SELECT [CompanyNo],[Eff_date], LAG( [Eff_date], 1, Null) OVER (PARTITION BY  [CompanyNo]
ORDER BY [Eff_date]) AS end_eff_date ,[rate] FROM [dbo].[Rate]) AS A
LEFT JOIN
[dbo].[Date] AS B
ON A.[CompanyNo]=B.[CompanyNo]
WHERE B.[date] BETWEEN  a.end_eff_date and a.[Eff_date] 

Answer 3

在 SQL Server 中，可以使用APPLY获取所需的一行：

select d.date, r.*
from date d outer apply
(select top (1) r.*
from rates r
where d.date >= r.eff_date
order by r.eff_date desc
) r;

你不需要LAG()或LEAD().APPLY实现了所谓的横向连接，非常强大。 如果只需要一列，则可以使用相关的子查询。您可以将横向联接视为相关子查询的泛化。