[SQL] 抓前後n個營業日

為了用最佳效能抓取營業日

目前系統架構只有記假日和補班日,
所以再建一個table(Workday date pirmary key,Idx int)
再加index設byIdx,
接下來只要寫一個排程, 每晚刪掉重建未來的營業日即可


CREATE PROCEDURE [dbo].[sInWorkdays](@Runday DATE = NULL)
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @date DATETIME= @Runday

        SELECT @date = MIN(Workday)
        FROM Workdays
        WHERE Workday > @date

        DECLARE @weekday INT

        DECLARE @Idx INT=
        (
            SELECT Idx
            FROM Workdays
            WHERE Workday = @date
        )

        DECLARE @maxDate DATE=
        (
            SELECT CONVERT(VARCHAR, YEAR(MAX(DT))) + '/12/31'
            FROM 放假檔 WITH(NOLOCK)
        )

        DELETE Workdays
        WHERE Workday > @date

        WHILE(@date < @maxDate)
            BEGIN
                SET @date = @date + 1
                SET @weekday = DATEPART(WEEKDAY, @date - 1)
                DECLARE @BUS_DAY INT=
                (
                    SELECT BUS_DAY
                    FROM 放假檔 WITH(NOLOCK)
                    WHERE DT = @date
                )--1:不放假/0:放假
                IF(@weekday < 6
                   OR ISNULL(@BUS_DAY, 1) = 1)
                    BEGIN
                        SET @Idx = @Idx + 1
                        INSERT Workdays
                        SELECT @date, 
                               @Idx
                    END
            END
    END
	GO

以下是取前後n個營業日的方法


/*
說明:	判斷是否為系統營業日
	1.@date 判斷基準日
		null 以getdate()帶入
	2.@addDay (可為正負數或0)
		1	:下一個營業日
		-1	:上一個營業日期
		0	:判斷當日是否為營業日,若為假日則帶回null,若為營業日則帶回@date
		null: 表示抓傳入日自己或之前最近的營業日

select  dbo.fGetWorkday(null,null)
*/

alter FUNCTION [dbo].[fGetWorkday](@date   DATE, 
                                    @addDay INT
)
RETURNS DATE
AS
     BEGIN
         SET @date = ISNULL(@date, GETDATE())
         DECLARE @offset INT=
         (
             SELECT Idx
             FROM Workdays
             WHERE Workday = @date
         )
         IF @addDay = 0
             RETURN IIF(@offset IS NULL, NULL, CONVERT(VARCHAR, @date, 111))

         IF @offset IS NULL
             IF @addDay < 0
                 SELECT @offset = MAX(Idx) + 1
                 FROM Workdays
                 WHERE Workday < @date
             ELSE
                 SELECT @offset = MIN(Idx) - 1
                 FROM Workdays
                 WHERE Workday > @date

         SET @offset = @offset + ISNULL(@addDay, 0)
         RETURN
         (
             SELECT CONVERT(VARCHAR, Workday, 111)
             FROM Workdays
             WHERE Idx = @offset
         )
     END
GO

Taiwan is a country. 臺灣是我的國家