[SQL] 工作天判斷

利用 CTE 產生完整日期資料表，搭配一個例外 Table 來輔助判斷，例外 Table 的設立主因是無法預測人事行政局到底是如何規劃假期、防災假等原因。

• 利用 201302 來說明，23 號上班日是補 15 號彈休。

DECLARE @SpecialTable Table ([Date] date , Memo varchar(100) , WorkDay int) 
INSERT INTO @SpecialTable VALUES
  ('20130211','春節',0) , 
  ('20130212','春節',0) ,
  ('20130213','春節',0) ,
  ('20130214','春節',0) ,
  ('20130215','春節',0) ,
  ('20130223','補 15 號彈休',1) ,
  ('20130228','228',0)

DECLARE 
  @StartDate AS date = '20130201' ,
  @EndDate AS date = '20130228'

-- 利用 CTE 產生完整的日期資料表，再跟例外 Table 進行整合判斷
; 
WITH CTE AS
(
  SELECT @StartDate AS [Date] 
  UNION ALL
  SELECT DATEADD(DD , 1 , [Date])
  FROM CTE
  WHERE Date < @EndDate
)
SELECT 
  T.[Date] , 
  DATENAME(dw,T.[Date]) + ISNULL('-' + S.Memo,'') AS Memo
FROM CTE AS T
  LEFT JOIN @SpecialTable AS S ON T.[Date] = S.[Date]
WHERE (S.[Date] IS NULL AND DATEPART(dw,T.[Date]) NOT IN (1,7)) -- 該天非例外資料且不是六日
  OR S.WorkDay = 1 -- 例外資料且為工作日

利用 CTE 是高耗 CPU 的作法，判斷工作天頻率高的話，建議把資料直接放在一個工作天 Table，另一種想法是建立假期 Table，不在假期 Table 內即為工作天。

延伸閱讀
• [SQL] 日期 - 最近的星期一

參考資料
• 論壇問題討論 1、2
• 產生民國年的 Time Table