[SQL]計算日期之間的工作日

2015-05-21

10497
0
SQL

要計算2個日期之間的工作日，SQL要如何寫呢?

一般要計算2個日期之間的工作日是將這2個日期之間的星期六、日及節日扣掉之後，就是剩下的就是工作日。

以 2015/05/01 到 2015/05/08 為例，

05/01 到 05/08 為 8 天

05/01 為勞動節

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

所以工作天數為 5天 (8 - 1 - 2), 2015/5/4 ~ 2015/5/8

那SQL要如何算呢?


--資料準備 
/* 
--假日檔
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/20', N'其他節');

*/


DECLARE @startDate  DATETIME, @endDate DATETIME
SELECT @startDate = '2015/05/01'  -- 開始日期
,@endDate = '2015/05/08' -- 結束日期
 
--05/01 到 05/08 為 8 天
;WITH DaysRange -- 所有日期
AS(
	SELECT @startDate AS WorkDay, DATEDIFF(DAY, @startDate, @endDate) AS DiffDays
	UNION ALL
	SELECT DATEADD(dd, 1, WorkDay),DiffDays -1
	FROM DaysRange wd
	WHERE DiffDays > 0
)
SELECT * 
FROM DaysRange;

--05/01 為 勞動節
SELECT *
FROM dbo.Holidays
WHERE HolidayDate >= @startDate 
AND HolidayDate <= @endDate;

--05/02, 05/03 為星期六、日
;WITH Days67 -- 星期六、日
AS(
	SELECT @startDate AS WorkDay, DATEDIFF(DAY, @startDate, @endDate) AS DiffDays
	UNION ALL
	SELECT DATEADD(dd, 1, WorkDay),DiffDays -1
	FROM Days67 wd
	WHERE DiffDays > 0
)
SELECT * 
FROM Days67
WHERE DATEPART(dw, WorkDay ) IN (1, 7);

所以將它們組合起來如下，


-- *** 組合起來 ***
DECLARE @startDate  DATETIME, @endDate DATETIME
SELECT @startDate = '2015/05/01'  -- 開始日期
,@endDate = '2015/05/08' -- 結束日期
 
--05/01 到 05/08 為 8 天
;WITH WorkDays -- 所有日期 扣掉 6、日及節日
AS(
	SELECT @startDate AS WorkDay, DATEDIFF(DAY, @startDate, @endDate) AS DiffDays
	UNION ALL
	SELECT DATEADD(dd, 1, WorkDay),DiffDays -1
	FROM WorkDays wd
	WHERE DiffDays > 0

)
SELECT * 
FROM WorkDays wd
WHERE DATEPART(dw, WorkDay ) IN (2,3, 4, 5, 6) -- 星期 1 ~ 5
AND NOT EXISTS (SELECT * FROM dbo.Holidays h WHERE h.HolidayDate = wd.WorkDay); -- 非節日