[SQL]開始日期加上工作天數，找出工作日

2015-05-21

8447
0
SQL

如果有開始日期，再給你一個工作天數，是否可以找出這些工作日呢?

我們可以依「計算日期之間的工作日」的方式，將工作天數再加上節日及星期六、日。

以開始日期為 2015/05/01 ，工作天數為 5 天

05/01 為勞動節

05/02, 05/03 為星期六、日

所以要加上 3 天

就會是 2015/05/01 ~ 2015/05/08

這樣子就可以了嗎???

如果 2015/05/08 是節日的話，那天數還要再加 1 天。

可是，前面我頂多只會先算到 2015/05/01 ~ 2015/05/05 之間的假日。

所以還要再看前面多出來的天數加進去後，再看有沒有假日，然後再加，再Check，再加 ..... 一直下去

我們可以用另一個做法，就是多取幾天出來，找出工作日，然後再依天數來取得那些工作日。

把邏輯列清楚後，我們就可以開始來寫SQL了，如下，

以開始日期為 2015/05/01 ，工作天數為 5 天

05/01 為勞動節

05/02, 05/03 為星期六、日

05/08 為臨時節日

所以工作日會是 5/4 ~ 5/7 及 5/11


/* 資料準備 
--假日檔
-- Drop TABLE Holidays;
CREATE TABLE Holidays
(
Id INT IDENTITY,
HolidayDate DATE,
HolidayName NVARCHAR(32) 
)
GO
INSERT INTO dbo.Holidays
        (HolidayDate, HolidayName )
VALUES  ('2015/05/01', N'勞動節');
INSERT INTO dbo.Holidays
        (HolidayDate, HolidayName )
VALUES  ('2015/05/08', N'臨時節日');
*/

DECLARE @startDate  DATETIME,
@workDays INT, @bufferDays INT;
SELECT @startDate = '2015/05/1'  -- 開始日期
,@workDays = 5 -- 工作天數為 5   
,@bufferDays = 30 -- 計算後面的 Buffer 的天數

;WITH WorkDays -- 所有日期
AS(
	SELECT @startDate AS WorkDay, @workDays + @bufferDays AS DiffDays 
	UNION ALL
	SELECT DATEADD(dd, 1, WorkDay),DiffDays -1
	FROM WorkDays wd
	WHERE DiffDays > 1
), HoldaysRange --星期 六 日及節日
AS (
SELECT *
FROM WorkDays wds
WHERE DATEPART(dw, wds.WorkDay ) IN (1, 7)
	OR EXISTS (SELECT * FROM dbo.Holidays h WHERE h.HolidayDate = wds.WorkDay)
), WorkDaysRange 
AS (
SELECT wd.*, ROW_NUMBER() OVER(ORDER BY wd.WorkDay) AS seq
FROM WorkDays wd
LEFT JOIN HoldaysRange hr
ON wd.WorkDay = hr.WorkDay
WHERE hr.WorkDay IS NULL
) 
SELECT * 
FROM WorkDaysRange wr
WHERE wr.seq <= @workDays
OPTION (MAXRECURSION 0);