SQLServerのストアドファンクションで営業日を踏まえた日数取得

Transact-SQL

ユーザ定義関数(ファンクション)

前提条件として営業日を判断できるカレンダマスタが必要

＜Mカレンダ＞

［項目名］　［型］

年月日　　　datetime

休日区分　　tinyint

シンプル版

--------------------------------------------------------------------------------------------------------------------------
--営業日取得
--内容：第一引数［日付］に対して、第二引数［日数］後の日付を取得
--------------------------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'FN ' AND name = 'func_BusinessDate')
    DROP FUNCTION func_BusinessDate
GO

CREATE FUNCTION [func_BusinessDate] (
     @ArgDate Date
    ,@AddBusinessDay smallint
)
Returns Date
As
Begin
  -- ローカル変数を宣言
  Declare @DateValue Date

    -- 営業日求める
    IF @AddBusinessDay = 0
        --ゼロの場合
        SET @DateValue = @ArgDate

    ELSE IF @AddBusinessDay >= 1
        --プラスの場合
        SELECT @DateValue = MAX(DDD.年月日)
        FROM (
              SELECT TOP (@AddBusinessDay)
                 年月日
              FROM
                 [Ｍカレンダ]
              WHERE
                     休日区分 = 0
                 AND 年月日 > @ArgDate
              ORDER BY 年月日
                 ) DDD;

    ELSE
        --マイナスの場合
        SELECT @DateValue = MIN(DDD.年月日)
        FROM (
              SELECT TOP (- (@AddBusinessDay-1) )
                 年月日
              FROM
                 [Ｍカレンダ]
              WHERE
                     休日区分 = 0
                 AND 年月日 <= @ArgDate
              ORDER BY 年月日 DESC
                 ) DDD;

  -- 戻り値セット
  Return (@DateValue)

End

--------------------------------------------------------------------------------------------------------------------------

--営業日取得

--内容：第一引数［日付］に対して、第二引数［日数］後の日付を取得

--------------------------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'FN ' AND name = 'func_BusinessDate')

DROP FUNCTION func_BusinessDate

CREATE FUNCTION [func_BusinessDate] (

@ArgDate Date

,@AddBusinessDay smallint

)

Returns Date

Begin

-- ローカル変数を宣言

Declare @DateValue Date

-- 営業日求める

IF @AddBusinessDay = 0

--ゼロの場合

SET @DateValue = @ArgDate

ELSE IF @AddBusinessDay >= 1

--プラスの場合

SELECT @DateValue = MAX(DDD.年月日)

FROM (

SELECT TOP (@AddBusinessDay)

年月日

FROM

[Ｍカレンダ]

WHERE

休日区分 = 0

AND 年月日 > @ArgDate

ORDER BY 年月日

) DDD;

ELSE

--マイナスの場合

SELECT @DateValue = MIN(DDD.年月日)

FROM (

SELECT TOP (- (@AddBusinessDay-1) )

年月日

FROM

[Ｍカレンダ]

WHERE

休日区分 = 0

AND 年月日 <= @ArgDate

ORDER BY 年月日 DESC

) DDD;

-- 戻り値セット

Return (@DateValue)

End

日付取得後チェックがある版

--------------------------------------------------------------------------------------------------------------------------
--営業日取得
--内容：第一引数［日付］に対して、第二引数［日数］後の日付を取得
--    ：日付チェック① → 前月となる場合は当月の第一営業日をセット
--    ：日付チェック② → 過去の日付になる場合は当日をセット
--------------------------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'FN ' AND name = 'func_BusinessDate2')
    DROP FUNCTION func_BusinessDate2
GO

CREATE FUNCTION [func_BusinessDate2] (
     @ArgDate Date
    ,@AddBusinessDay smallint
)
Returns Date
As
Begin
  -- ローカル変数を宣言
  Declare @DateValue Date

    -- 営業日求める
    IF @AddBusinessDay = 0
        --ゼロの場合
        SET @DateValue = @ArgDate

    ELSE IF @AddBusinessDay >= 1
        --プラスの場合
        SELECT @DateValue = MAX(DDD.年月日)
        FROM (
              SELECT TOP (@AddBusinessDay)
                 年月日
              FROM
                 [Ｍカレンダ]
              WHERE
                     休日区分 = 0
                 AND 年月日 > @ArgDate
              ORDER BY 年月日
                 ) DDD;

    ELSE
        --マイナスの場合
        SELECT @DateValue = MIN(DDD.年月日)
        FROM (
              SELECT TOP (- (@AddBusinessDay-1) )
                 年月日
              FROM
                 [Ｍカレンダ]
              WHERE
                     休日区分 = 0
                 AND 年月日 <= @ArgDate
              ORDER BY 年月日 DESC
                 ) DDD;


    -------------------------------------------------------------
    --日付チェック① → 前月となる場合は当月の第一営業日をセット
    IF DATEDIFF(MONTH, @ArgDate, @DateValue) < 0
        --当月の第一営業日をセット
        SELECT @DateValue = MIN(DDD.年月日)
        FROM (
              SELECT TOP (1)
                 年月日
              FROM
                 [Ｍカレンダ]
              WHERE
                     休日区分 = 0
                 AND CONVERT(VARCHAR(6), 年月日, 112) = CONVERT(VARCHAR(6), GETDATE(), 112)
              ORDER BY 年月日
                 ) DDD;

    --日付チェック② → 過去の日付になる場合は当日をセット
    IF @DateValue < CONVERT(date, GETDATE())
        SET @DateValue = CONVERT(date, GETDATE())

  -- 戻り値セット
  Return (@DateValue)

End

--------------------------------------------------------------------------------------------------------------------------

--営業日取得

--内容：第一引数［日付］に対して、第二引数［日数］後の日付を取得

-- ：日付チェック① → 前月となる場合は当月の第一営業日をセット

-- ：日付チェック② → 過去の日付になる場合は当日をセット

--------------------------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'FN ' AND name = 'func_BusinessDate2')

DROP FUNCTION func_BusinessDate2

CREATE FUNCTION [func_BusinessDate2] (

@ArgDate Date

,@AddBusinessDay smallint

)

Returns Date

Begin

-- ローカル変数を宣言

Declare @DateValue Date

-- 営業日求める

IF @AddBusinessDay = 0

--ゼロの場合

SET @DateValue = @ArgDate

ELSE IF @AddBusinessDay >= 1

--プラスの場合

SELECT @DateValue = MAX(DDD.年月日)

FROM (

SELECT TOP (@AddBusinessDay)

年月日

FROM

[Ｍカレンダ]

WHERE

休日区分 = 0

AND 年月日 > @ArgDate

ORDER BY 年月日

) DDD;

ELSE

--マイナスの場合

SELECT @DateValue = MIN(DDD.年月日)

FROM (

SELECT TOP (- (@AddBusinessDay-1) )

年月日

FROM

[Ｍカレンダ]

WHERE

休日区分 = 0

AND 年月日 <= @ArgDate

ORDER BY 年月日 DESC

) DDD;

-------------------------------------------------------------

--日付チェック① → 前月となる場合は当月の第一営業日をセット

IF DATEDIFF(MONTH, @ArgDate, @DateValue) < 0

--当月の第一営業日をセット

SELECT @DateValue = MIN(DDD.年月日)

FROM (

SELECT TOP (1)

年月日

FROM

[Ｍカレンダ]

WHERE

休日区分 = 0

AND CONVERT(VARCHAR(6), 年月日, 112) = CONVERT(VARCHAR(6), GETDATE(), 112)

ORDER BY 年月日

) DDD;

--日付チェック② → 過去の日付になる場合は当日をセット

IF @DateValue < CONVERT(date, GETDATE())

SET @DateValue = CONVERT(date, GETDATE())

-- 戻り値セット

Return (@DateValue)

End