SAPB1 Get Working Day Difference 計算工作日

SAPB1 Get Working Day Difference 計算工作日

Author : Willie
Date: 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