SAPB1 Get Working Day Difference 計算工作日
Author : WillieDate: 2016/04/14
----------------------------------------------------------------------
由於工作上要用到計算SAPB1裡的工作日以推估交期，因此做了底下的Function
利用傳入二個參數(啟始日，結束日) 來回傳工作天數。
結果加下圖，有正向及反向的輸入會呈現正數/負數的回傳值。
整個程式的大方向是利用DateDiff計算日期區間的天數後再跑迴圈逐一判斷是否為假日，
最後的回傳值利用DateDiff結果+-假日天數。
其中引用了Will 保哥的SQL技巧來設計此Function。
1: CREATE FUNCTION [dbo].[My_GetWorkingDayDiff_F]
2: (
3: -- Add the parameters for the function here
4: @p_StartDate datetime,
5: @p_EndDate datetime
6: )
7: RETURNS int
8: /************************************************************
9: * 程式名稱: My_GetWorkingDayDiff_F
10: * 報表名稱:
11: * 目 的: 判斷 B1年度假日和周末, 回傳工作天
12: * 作 者: Willie
13: * 撰寫日期: 2016/04/13
14: * 參數說明: @p_StartDate=起始日期, @p_EndDate=結束日期
15: * 回 傳: Days (int)
16: SELECT dbo.[RA_GetWorkingDayDiff_F]('2015-04-29', '2014-05-03') --輸入日期, 也可以用getdate()
17: (4/29~5/2 Labor Day, RA Office is closed)
18: ************************************************************/
19: AS
20: BEGIN
21: -- Declare the return variable here
22: declare @intCount int, @intDayOff int,@resultDay int ----計算指日期指標
23: declare @date datetime ---- 計算中日期
24: Declare @DayDiff int;
25: Declare @flagPositive BIT;
26: -- --------------------------------------------------
27: -- Init
28: SET @DayDiff = DATEDIFF(day,@p_StartDate,@p_EndDate);
29: SET @flagPositive = 1;
30: IF @DayDiff < 0
31: SET @flagPositive = 0;
32: IF @p_EndDate < @p_StartDate
33: set @date = @p_EndDate
34: ELSE
35: set @date = @p_StartDate
36: SET @intCount = 0;
37: SET @intDayOff = 0;
38: -- --------------------------------------------------
39: WHILE ( @intCount <ABS(@DayDiff) AND @p_StartDate<>@p_EndDate )-------------- loop begin
40: BEGIN
41: SET @date = DATEADD(DAY, 1, @date) ---- @p_StartDate 開始日期
42: --Is Weekend
43: IF (DATEDIFF( DAY, '17530101', @date )% 7/5 ) = 1 -- If begin
44: SET @intDayOff = @intDayOff+1
45: ELSE
46: BEGIN
47: -- 取得SAPB1裡的假日設定
48: IF ( SELECT COUNT(EndDate)
49: FROM HLD1
50: WHERE HldCode = YEAR(@date)
51: AND (CAST(@date As datetime) BETWEEN StrDate AND EndDate) ) >= 1
52: BEGIN
53: SET @intDayOff = @intDayOff+1
54: END
55: END
56: -- endif
57: set @intCount = @intCount+1
58: END -------------- loop end
59: If @flagPositive =1
60: SET @resultDay = ( @DayDiff - @intDayOff );
61: ELSE
62: SET @resultDay = ( @DayDiff +@intDayOff );
63: -------------------------------------------------
64: RETURN @resultDay;
65: END
~ Thank You ~
Update:
2017/01/03 修正第51行，當>= 1時即為在SAP上設定的假日
Reference:
[1] Will 保哥, 如何用簡單的 SQL 技巧取得特定日期是否為週末假日 http://blog.miniasp.com/post/2009/01/20/How-to-use-simple-T-SQL-to-get-weekend-status-in-date.aspx
