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)