sql-server-2008 - 从某个开始日期开始按工作日数计算日期

Question

我有两个表，一个项目表和一个日历表。第一个包含开始日期和所需天数。日历表包含通常的日期信息，如日期、星期几，还有一列是工作日，它显示这一天是星期六、星期日或银行假日(值 = 0)还是正常工作日(值 = 1)。

对于某个报告，我需要编写一个存储过程，通过添加所需的估计工作日数来计算预测的结束日期。

例子:

**Projects**
Name         Start_Planned  Work_days_Required
Project A    02.05.2016     6

Calendar (04.05 is a bank holdiday)
Day          Weekday        Workingday
01.05.2016   7              0
02.05.2016   1              1    
03.05.2016   2              1
04.05.2016   3              0
05.05.2016   4              1
06.05.2016   5              1
07.05.2016   6              0
08.05.2016   7              0
09.05.2016   1              1
10.05.2016   2              1

假设，所需的估计天数为 6(这导致预计结束日期为 2016 年 5 月 10 日)。是否有可能以某种方式连接表格，这让我可以放置类似

的内容

select date as enddate_predicted
from calendar
join projects
where number_of_days = 6

我会发布更多代码，但我完全不知道从哪里开始。

谢谢!

Answer 1

您可以获取第一次约会后的所有工作日，然后应用 ROW_NUMBER() 获取每个日期的天数:

SELECT  Date, DayNum = ROW_NUMBER() OVER(ORDER BY Date)
FROM    Calendar
WHERE   IsWorkingDay = 1
AND     Date >= @StartPlanned

那么这只是第 6 天的过滤情况:

DECLARE @StartPlanned DATE = '20160502',
        @Days INT = 6;

SELECT  Date
FROM    (   SELECT  Date, DayNum = ROW_NUMBER() OVER(ORDER BY Date)
            FROM    Calendar
            WHERE   WorkingDay = 1
            AND     Date >= @StartPlanned
        ) AS c
WHERE   c.DayNum = @Days;

这不是问题的一部分，但为了将来证明，这在 SQL Server 2012+ 中使用 OFFSET/FETCH

更容易实现

DECLARE @StartPlanned DATE = '20160502',
        @Days INT = 6;

SELECT  Date
FROM    dbo.Calendar
WHERE   Date >= @StartPlanned
AND     WorkingDay = 1
ORDER BY Date
OFFSET (@Days - 1) ROWS FETCH NEXT 1 ROWS ONLY

附录

我错过了之前关于有另一个表的部分，关于将它放入游标的评论促使我修改我的答案。我会在您的日历表中添加一个名为 WorkingDayRank 的新列:

ALTER TABLE dbo.Calendar ADD WorkingDayRank INT NULL;
GO
UPDATE  c
SET     WorkingDayRank = wdr
FROM    (   SELECT  Date, wdr = ROW_NUMBER() OVER(ORDER BY Date)
            FROM    dbo.Calendar
            WHERE   WorkingDay = 1
        ) AS c;

这可以即时完成，但将其存储为值会获得更好的性能，然后您的查询将变为:

SELECT  p.Name,
        p.Start_Planned,
        p.Work_days_Required,
        EndDate = c2.Date
FROM    Projects AS P
        INNER JOIN dbo.Calendar AS c1
            ON c1.Date = p.Start_Planned
        INNER JOIN dbo.Calendar AS c2
            ON c2.WorkingDayRank = c1.WorkingDayRank + p.Work_days_Required - 1;

这只是获取开始日期的工作日排名，并通过加入 WorkingDayRank 找到项目提前指定的天数(-1 因为您希望结束日期包含范围)

如果您计划在非工作日开始您的项目，这将失败，因此更可靠的解决方案可能是:

SELECT  p.Name,
        p.Start_Planned,
        p.Work_days_Required,
        EndDate = c2.Date
FROM    Projects AS P
        CROSS APPLY
        (   SELECT  TOP 1 c1.Date, c1.WorkingDayRank
            FROM    dbo.Calendar AS c1
            WHERE   c1.Date >= p.Start_Planned
            AND     c1.WorkingDay = 1
            ORDER BY c1.Date
        ) AS c1
        INNER JOIN dbo.Calendar AS c2
            ON c2.WorkingDayRank = c1.WorkingDayRank + p.Work_days_Required - 1;

这使用 CROSS APPLY 获取项目开始日期或之后的下一个工作日，然后应用与之前相同的连接。