使用日期维度日历减去工作日

Question

许多评论建议在SQL Server中创建一个日期表用于工作日和假日计算，这是个好主意！我发现了一篇很棒的如何在SQL Server中创建这样一组表的文章——https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/.如果你需要做大量的日期计算，我强烈推荐这篇文章。

从这些表中，我创建了一个名为USCalendar的视图，它可以计算结束日期，给定开始日期(03/01/21)和工作日数(180)，效果非常好！

select Top 1 TheDate as EndDate
from (
select Top 180 TheDate from USCalendar
Where TheDate>='03/01/2021'
And IsWeekend=0 and IsHoliday=0
Order By TheDate
) as BusinessDays
Order By TheDate DESC

我唯一的挑战是想出一种扭转局面的方法——如果我知道目标结束日期和工作日数，我该如何找到开始日期？

Answer 1

如果你只想要一个日期，那么你实际上并不需要所有这些

你可以简单地做

select TheDate as EndDate
from USCalendar
Where TheDate >= '03/01/2021'
And IsWeekend = 0 and IsHoliday = 0
Order By TheDate
OFFSET 179 ROWS
FETCH NEXT 1 ROW ONLY;

为了扭转这种局面，我们将其更改为<=，并按另一个方向排序：

select TheDate as EndDate
from USCalendar
Where TheDate <= '03/01/2021'
And IsWeekend = 0 and IsHoliday = 0
Order By TheDate DESC
OFFSET 179 ROWS
FETCH NEXT 1 ROW ONLY;

我建议您将其作为内联表值函数，这样您就可以将任何日期放入其中。它基本上变成了一个参数化视图。它被内联到外部查询中，因此非常高效：

CREATE /* OR ALTER */ FUNCTION dbo.GetBusinessEndDate
( @StartDate date, @numberOfDays int )
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
select TheDate as EndDate
from USCalendar
Where TheDate >= @StartDate
And IsWeekend = 0 and IsHoliday = 0
Order By TheDate
OFFSET @numberOfDays ROWS
FETCH NEXT 1 ROW ONLY
);

你像使用任何其他表或视图一样使用它，你也可以CROSS APPLY它。

我建议为StartDate制作一个单独的函数，不要试图将其组合。