如何從 SQL Server 查詢中的 start_date 和 duration 計算 end_date

我有一個包含 4 個欄位的表。開始日期、持續時間、結束日期、實際日期。

我提供 start_date 和 duration 作為計算結束日期的輸入。

在計算 end_date 時，不考慮週六和周日。

此外，如果 actual_date 具有值，則該值將保存為 end_date。

範例：Start_date = 18/05/2020，持續時間：10。因此，應通過排除 23/05/2020 和 24/05/2020 來計算 End_date。

我嘗試了不同的方法來歸檔它。

以下是列 end_date 的更改查詢。

這滿足了我一半的要求，但不知道如何從 start_date 和持續時間中排除週末（週六和周日）。

ALTER TABLE [DBName].[dbo].[TestDate] ADD End_Date as CAST(
CASE 
WHEN (Actual_Date='' or Actual_Date IS NULL) then  DATEADD(day, Duration, Start_Date)
ELSE Actual_Date
END as DATE
)
GO

請幫助我。

我相信您正在尋找的程式碼是

ALTER TABLE [DBName].[dbo].[TestDate] ADD End_Date as  
   DATEADD(D, 
           7 * (Duration / 5) -- Business weeks as calendar days
           + CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) -- Move weekend start to Friday, if needed
           + (Duration % 5) -- Day outside of full business weeks
           +IIF(DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6, 2, 0) -- Weekend Jump
           , StartDate
       )
GO

基本上，這將整個工作周和之後的額外日子分開。但是，如果您在開始時考慮這些額外的日子，您只需要知道它們是否超過了星期五，如果是，則添加一些日子。為簡化起見，我們首先將周六/週日移至週五（您可以在這兩個地方使用 1,0,0,0,0,0,2 來改為移至週一），然後如果我們結束了，則添加 2 天在周末。

如果您想對最終結果進行 QA，此程式碼將為您提供所有要查看和驗證的組件：

WITH TestStartDates AS (
   SELECT TOP (7) 
       DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT 1)), CAST('2020-05-18' AS DATE)) as StartDate
   FROM Sys.Columns
),
TestAddDays AS (
       SELECT TOP (45) 
       ROW_NUMBER() OVER (ORDER BY (SELECT 1)) Duration
   FROM Sys.Columns
)
SELECT 
   StartDate
   , DATENAME(DW, StartDate) as Weekday
   , CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) OffsetWeekend
   , DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1), StartDate) WeekendToFriday
   , Duration
   , (Duration / 5) as WeeksToAdd
   , (Duration % 5) DaysOverBusinessWeeks
   , DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) StartDateWeekdayNum
   , CASE WHEN DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6 THEN 2 ELSE 0 END InitialWeekendJump -- 8 = If we pass Friday, jump 2
   , DATEADD(D, 7 * (Duration / 5) + CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) + (Duration % 5) + IIF(DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6, 2, 0), StartDate) as EndDate
   , DATENAME(DW, DATEADD(D, 7 * (Duration / 5) + CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) + (Duration % 5) + IIF(DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6, 2, 0), StartDate)) AS EndWeekDay
FROM TestAddDays CROSS JOIN TestStartDates
ORDER BY Duration, StartDate

迭代解決方案：

WITH cte AS ( SELECT id, 
                    DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, start_date) > 5 
                                      THEN 9 - DATEPART(WEEKDAY, start_date) 
                                      ELSE 1 
                                      END, start_date) cur_date, duration-1 reminder
             FROM test
             UNION ALL
             SELECT id, 
                    DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, cur_date) > 5 
                                      THEN 3 
                                      ELSE 1 
                                      END, cur_date), 
                    reminder - 1
             FROM cte
             WHERE reminder > 0 )
UPDATE test
SET test.end_date = cte.cur_date
FROM cte
WHERE test.id = cte.id
 AND cte.reminder = 0;

小提琴

PD。它幾乎不是最優的……對於大duration值，你可以理解。將其除以 7 並根據周數相加（每個 5 天），然後按 int 迭代。僅提醒。

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