SAP B1 中计算工作日和假期

SAP袁英杰

已于 2024-04-02 15:18:46 修改

阅读量411

点赞数 3

分类专栏： SAP B1 文章标签：数据库 sql

于 2024-04-01 18:09:54 首次发布

本文链接：https://blog.csdn.net/yuan278501381/article/details/137242172

版权

SAP B1 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

该篇文章介绍了三个SQL函数，用于获取未来2048天内的预设假期、特定工作日以及考虑需求日期和提前期的最近工作日，适用于SAP系统中处理节假日管理的场景。

摘要由CSDN通过智能技术生成

1、获得所有假期

-- =============================================
-- Author:  袁英杰
-- Create date: 2023-08-27
-- Description:  1、默认返回未来2048天内的所有假期，包括周末和自定义假期（SAP中假期的设置）
--2、如果假期和第一条无关（比如在自定义表、后台表中设置的假期），那么可以改写函数，但是函数返回的字段名不得更改
--
-- =============================================
CREATE FUNCTION getHld ()
   RETURNS @Result TABLE (Hlday DATETIME)
AS
BEGIN
   DECLARE
      @WndFrm      INT,
      @WndTo       INT,
      @isCurYear   NVARCHAR (1),
      @ignrWnk     NVARCHAR (1)
   SELECT @WndFrm = WndFrm,
          @WndTo = WndTo,
          @isCurYear = isCurYear,
          @ignrWnk = ignrWnd
   FROM OHLD t
   WHERE t.HldCode = (SELECT HldCode FROM OADM)

   INSERT INTO @Result
      SELECT Date
      FROM (SELECT t.number,
                   t.Date,
                   DATEPART (WEEKDAY, t.Date) WeekDay,
                   CASE
                      WHEN @ignrWnk = 'Y'
                      THEN
                         N'N'
                      ELSE
                         CASE
                            WHEN     @WndFrm = @WndTo
                                 AND DATEPART (WEEKDAY, t.Date) = @WndFrm
                            THEN
                               N'Y'
                            WHEN     @WndFrm > @WndTo
                                 AND (   DATEPART (WEEKDAY, t.Date) >=
                                         @WndFrm
                                      OR DATEPART (WEEKDAY, t.Date) <= @WndTo)
                            THEN
                               N'Y'
                            WHEN     @WndFrm < @WndTo
                                 AND (DATEPART (WEEKDAY, t.Date) BETWEEN @WndFrm
                                                                     AND @WndTo)
                            THEN
                               N'Y'
                            ELSE
                               'N'
                         END
                   END isWeekDay,
                   t1.StrDate,
                   t1.EndDate
            --CONVERT (NVARCHAR (5), t.Date, 101)
            FROM (SELECT t.Number,
                         CONVERT (DATE, DATEADD (day, t.number, GETDATE ())) Date
                  FROM master..spt_values t
                  WHERE t.type = 'P'                  /*AND t.number <= 1000*/
                                    ) t
                 LEFT JOIN (SELECT StrDate, EndDate
                            FROM HLD1
                            WHERE HldCode = (SELECT HldCode FROM OADM)) t1
                    ON     CASE WHEN @isCurYear = 'Y' THEN t.Date ELSE '' END >=
                           CASE
                              WHEN @isCurYear = 'Y' THEN t1.StrDate
                              ELSE ''
                           END
                       AND CASE WHEN @isCurYear = 'Y' THEN t.Date ELSE '' END <=
                           CASE
                              WHEN @isCurYear = 'Y' THEN t1.EndDate
                              ELSE ''
                           END
                       AND CASE
                              WHEN @isCurYear = 'N'
                              THEN
                                 CONVERT (NVARCHAR (5), t.Date, 101)
                              ELSE
                                 ''
                           END >=
                           CASE
                              WHEN @isCurYear = 'N'
                              THEN
                                 CONVERT (NVARCHAR (5), t1.StrDate, 101)
                              ELSE
                                 ''
                           END
                       AND CASE
                              WHEN @isCurYear = 'N'
                              THEN
                                 CONVERT (NVARCHAR (5), t.Date, 101)
                              ELSE
                                 ''
                           END <=
                           CASE
                              WHEN @isCurYear = 'N'
                              THEN
                                 CONVERT (NVARCHAR (5), t1.EndDate, 101)
                              ELSE
                                 ''
                           END) t
      WHERE t.isWeekDay = 'Y' OR t.StrDate IS NOT NULL
      ORDER BY t.Date


   RETURN
END

2、获得工作日

-- =============================================
-- Author:  袁英杰
-- Create date: 2023-08-27
-- Description:  1、传入起始日期、工作日(天数)，从未来2048天内返回最近一个工作日和相应的结束日期
-- =============================================
CREATE FUNCTION getWrkDay (@DteFrm DATETIME, @Days INT)
   RETURNS @Result TABLE (DteFrm DATETIME, DteTo DATETIME)
AS
BEGIN
   INSERT INTO @Result
      SELECT min (t.Date) DteFrm, max (t.Date) DteTo
      FROM (SELECT TOP (@Days) *
            FROM (SELECT t.Number,
                         CONVERT (DATE, DATEADD (day, t.number, GETDATE ())) Date
                  FROM master..spt_values t
                  WHERE t.type = 'P'                  /*AND t.number <= 1000*/
                                    ) t
                 LEFT JOIN getHld () t1 ON t.Date = t1.Hlday
            WHERE t1.Hlday IS NULL) t

   RETURN
END

3、考虑需求日期、提前期，返回最近一个工作日

-- =============================================
-- Author:  袁英杰
-- Create date: 2023-08-27
-- Description:  传入需求日期、提前期，返回最近一个工作日(比如按MRP的BaseDue计算建议下单日期)
-- =============================================
CREATE FUNCTION getReleaseDay (@Dteto DATETIME, @Days INT)
   RETURNS TABLE
AS
RETURN (SELECT min (t.Date) ReleaseDt
        FROM (SELECT TOP (@Days) *
              FROM (SELECT t.Number,
                           CONVERT
                           (DATE, DATEADD (day, t.number, GETDATE ())) Date
                    FROM master..spt_values t
                    WHERE t.type = 'P'                /*AND t.number <= 1000*/
                                      ) t
                   LEFT JOIN getHld () t1 ON t.Date = t1.Hlday
              WHERE t1.Hlday IS NULL AND t.Date <= @Dteto
              ORDER BY t.Date DESC) t)