排除週末和節假日的 SQL 腳本

我有一個查詢，我需要根據條件向日期變數添加 2 天。

但是，此日期變數需要跳過週末和節假日。

我有一張儲存假期的桌子。如何將其集成到我的查詢中？

所以目前我檢查日期是否是周末。如果是這樣，那麼我會增加 1 或 2 天。但是，如果該日期是假期，那麼我需要添加另一天。然後檢查該日期是假期還是周末！

有沒有我可以用來做這個的腳本？

這確實是一個 T-SQL 問題，在 StackOverflow 上會更好。

也就是說，您可以使用以下方法來確定日期是否在周末：

SELECT DATENAME(dw,GETDATE()) -- Friday
SELECT DATEPART(dw,GETDATE()) -- 6

並且確定日期是否在假期（通過查詢您的假期表）應該是微不足道的。

我建議你自己去刺。如果它有效，那就太好了 - 如果沒有，請發布您到目前為止所擁有的內容，以及您在 SO 上的具體問題。

使用最方便的日曆表，該表包含每天的一行，帶有自定義標記（如IsHoliday、IsFirstOfMonth、IsFirstMondayOfMonth等），以及您可能需要的任何其他列（如BusinessHourStartTime、BusinessHourEndTime等）。

您可以在 this 之後遞歸地生成一個SELECT。

SET DATEFIRST 1 -- 1: Monday, 7: Sunday

DECLARE @StartDate DATE = '2018-01-01'
DECLARE @EndDate DATE = '2025-01-01'

DECLARE @HolidaysTbl as TABLE (HolidayDate date)
INSERT INTO @HolidaysTbl(HolidayDate) VALUES('2018-12-13'), ('2018-12-17'), ('2018-12-19');

;WITH GeneratedDates AS
(
   SELECT
       GeneratedDate = @StartDate

   UNION ALL

   SELECT
       GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate)
   FROM
       GeneratedDates AS G
   WHERE
       G.GeneratedDate < @EndDate
),
SpecialDays AS
(
   SELECT
       Date = G.GeneratedDate,
       Year = YEAR(G.GeneratedDate),
       Month = MONTH(G.GeneratedDate),
       Day = DAY(G.GeneratedDate),
       IsWeekend = CASE WHEN DATEPART(WEEKDAY, G.GeneratedDate) IN (6, 7) THEN 1 ELSE 0 END,
       IsHoliday = CASE WHEN H.HolidayDate IS NOT NULL THEN 1 ELSE 0 END
   FROM
       GeneratedDates AS G
       LEFT JOIN @HolidaysTbl AS H ON G.GeneratedDate = H.HolidayDate
)
SELECT
   Date = S.Date,
   Year = S.Year,
   Month = S.Month,
   Day = S.Day,
   IsWeekend = S.IsWeekend,
   IsHoliday = S.IsHoliday,
   BusinessHoursStartTime = CASE WHEN S.IsHoliday = 0 AND S.IsWeekend = 0 THEN CONVERT(TIME, '09:00') END,
   BusinessHoursEndTIme = CASE WHEN S.IsHoliday = 0 AND S.IsWeekend = 0 THEN CONVERT(TIME, '18:00') END,
   WorkingDaysOrder = CASE 
       WHEN S.IsHoliday = 0 AND S.IsWeekend = 0 
       THEN ROW_NUMBER() OVER (
           ORDER BY 
           S.IsHoliday ASC,
           S.IsWeekend ASC,
           S.Date ASC) END
FROM
   SpecialDays AS S
OPTION
   (MAXRECURSION 0)

生成這樣的行：

Date        Year    Month   Day     IsWeekend   IsHoliday   BusinessHoursStartTime  BusinessHoursEndTIme    WorkingDaysOrder
2018-01-01  2018    1       1       0           0           09:00:00.0000000        18:00:00.0000000        1
2018-01-02  2018    1       2       0           0           09:00:00.0000000        18:00:00.0000000        2
2018-01-03  2018    1       3       0           0           09:00:00.0000000        18:00:00.0000000        3
2018-01-04  2018    1       4       0           0           09:00:00.0000000        18:00:00.0000000        4
2018-01-05  2018    1       5       0           0           09:00:00.0000000        18:00:00.0000000        5
2018-01-06  2018    1       6       1           0           NULL                    NULL                    NULL
2018-01-07  2018    1       7       1           0           NULL                    NULL                    NULL
2018-01-08  2018    1       8       0           0           09:00:00.0000000        18:00:00.0000000        6
2018-01-09  2018    1       9       0           0           09:00:00.0000000        18:00:00.0000000        7
2018-01-10  2018    1       10      0           0           09:00:00.0000000        18:00:00.0000000        8
2018-01-11  2018    1       11      0           0           09:00:00.0000000        18:00:00.0000000        9
2018-01-12  2018    1       12      0           1           NULL                    NULL                    NULL
2018-01-13  2018    1       13      1           0           NULL                    NULL                    NULL
2018-01-14  2018    1       14      1           0           NULL                    NULL                    NULL
2018-01-15  2018    1       15      0           0           09:00:00.0000000        18:00:00.0000000        10
2018-01-16  2018    1       16      0           0           09:00:00.0000000        18:00:00.0000000        11
2018-01-17  2018    1       17      0           0           09:00:00.0000000        18:00:00.0000000        12
2018-01-18  2018    1       18      0           0           09:00:00.0000000        18:00:00.0000000        13
2018-01-19  2018    1       19      0           0           09:00:00.0000000        18:00:00.0000000        14
2018-01-20  2018    1       20      1           0           NULL                    NULL                    NULL
2018-01-21  2018    1       21      1           0           NULL                    NULL                    NULL
2018-01-22  2018    1       22      0           0           09:00:00.0000000        18:00:00.0000000        15
2018-01-23  2018    1       23      0           0           09:00:00.0000000        18:00:00.0000000        16

因此，當您需要添加天數並且結果必須是工作日時，您可以簡單地在表中找到您查詢的日期，檢索它WorkingDaysOrder，添加 2 天（例如）並帶回它的關聯Date。

DECLARE @DateToAddDays DATE = '2018-01-10' -- Wednesday
DECLARE @AmountOfDaysToAdd INT = 2

SELECT 
   D.Date 
FROM
   Calendar AS D
WHERE
   D.WorkingDaysOrder = @AmountOfDaysToAdd + (SELECT T.WorkingDaysOrder FROM Calendar AS T WHERE T.Date = @DateToAddDays)

-- Result: 2018-01-15 (Monday, day 12 is Holiday, 13 and 14 weekend)

日曆表將幫助您解決大多數日期問題，例如計算兩個日期之間的工作時間或查找每個月的第三個星期日。它沒有很多行，使日期和時間處理非常易於閱讀和維護。

引用自：https://dba.stackexchange.com/questions/143258