---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第10章 ユーザー定義関数
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 10.1 ユーザー定義スカラ関数の設計
---------------------------------------------------------------------

CREATE FUNCTION util.fnQuarterString
(
    @dtmDate DATETIME
)
RETURNS CHAR(6) -- "3Q2000"形式で四半期を返す
AS
BEGIN
    RETURN (DATENAME(q, @dtmDate) + 'Q' + DATENAME(yyyy, @dtmDate))
END


SELECT util.fnQuarterString(GETDATE())


CREATE FUNCTION util.fnThreeBusDays
(
    @dtmDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @inyDayOfWeek TINYINT
    SET @inyDayOfWeek = DATEPART(dw, @dtmDate)
    SET @dtmDate = CONVERT(DATETIME, CONVERT(VARCHAR, @dtmDate, 101))
    IF @inyDayOfWeek = 1 -- 日曜
        RETURN DATEADD(d, 3, @dtmDate)
    IF @inyDayOfWeek = 7 -- 土曜
        RETURN DATEADD(d, 4, @dtmDate)
    IF @inyDayOfWeek = 6 -- 金曜
        RETURN DATEADD(d, 5, @dtmDate)
    IF @inyDayOfWeek = 5 -- 木曜
        RETURN DATEADD(d, 5, @dtmDate)
    IF @inyDayOfWeek = 4 -- 水曜
        RETURN DATEADD(d, 5, @dtmDate)

    RETURN DATEADD(d, 3, @dtmDate)
END

---------------------------------------------------------------------
-- 10.2 複数ステートメントテーブル値関数
---------------------------------------------------------------------

SELECT DD.TermId, DD.DueDate, Inventory.Lease
FROM dbo.fnDueDays('2005/12/1', '2009/12/1', 'monthly') DD, Inventory
WHERE InventoryId = 8
AND DD.DueDate > GETDATE()


CREATE TABLE #tbl(TermId INT, DueDate SMALLDATETIME)

INSERT INTO #tbl(TermId, DueDate)
    EXEC dbo.ap_Terms_List '2005/12/1', '2009/12/1', 'monthly'

SELECT #tbl.TermId, #tbl.DueDate, Inventory.Lease
FROM #tbl, Inventory
WHERE InventoryId = 8
AND #tbl.DueDate > GETDATE()

DROP TABLE #tbl


CREATE FUNCTION dbo.fnDueDays
(
    @dtsStartDate SMALLDATETIME,
    @dtsEndDate SMALLDATETIME,
    @chvLeaseFrequency VARCHAR(20)
)
RETURNS @tblTerms TABLE
    (
        TermID INT,
        DueDate SMALLDATETIME
    )
AS
BEGIN
    DECLARE @insTermsCount SMALLINT -- 支払い日の総数
    DECLARE @insTerms SMALLINT -- 支払い間隔

    -- 期間の値を計算する
    SELECT @insTermsCount =
        CASE @chvLeaseFrequency
            WHEN 'monthly'
                THEN DATEDIFF(month, @dtsStartDate, @dtsEndDate)
            WHEN 'semi-monthly'
                THEN 2 * DATEDIFF(month, @dtsStartDate, @dtsEndDate)
            WHEN 'bi-weekly'
                THEN DATEDIFF(week, @dtsStartDate, @dtsEndDate)/2
            WHEN 'weekly'
                THEN DATEDIFF(week, @dtsStartDate, @dtsEndDate)
            WHEN 'quarterly'
                THEN DATEDIFF(qq, @dtsStartDate, @dtsEndDate)
            WHEN 'yearly'
                THEN DATEDIFF(y, @dtsStartDate, @dtsEndDate)
        END

    -- 支払い日の一覧を生成する
    SET @insTerms = 1
    WHILE @insTerms <= @insTermsCount
    BEGIN
        INSERT @tblTerms
        (
            TermID,
            DueDate
        )
        VALUES
        (
            @insTerms,
            CONVERT
            (
                SMALLDATETIME,
                CASE
                    WHEN @chvLeaseFrequency = 'monthly'
                        THEN DATEADD(month, @insTerms, @dtsStartDate)
                    WHEN @chvLeaseFrequency = 'semi-monthly'
                    AND @insTerms/2 = CAST(@insTerms AS FLOAT)/2
                        THEN DATEADD(month, @insTerms/2, @dtsStartDate)
                    WHEN @chvLeaseFrequency = 'semi-monthly'
                    AND @insTerms/2 <> CAST(@insTerms AS FLOAT)/2
                        THEN DATEADD(dd, 15,
                            DATEADD(month, @insTerms/2, @dtsStartDate))
                    WHEN @chvLeaseFrequency = 'bi-weekly'
                        THEN DATEADD(week, @insTerms*2, @dtsStartDate)
                    WHEN @chvLeaseFrequency = 'weekly'
                        THEN DATEADD(week, @insTerms, @dtsStartDate)
                    WHEN @chvLeaseFrequency = 'quarterly'
                        THEN DATEADD(qq, @insTerms, @dtsStartDate)
                    WHEN @chvLeaseFrequency = 'yearly'
                        THEN DATEADD(y, @insTerms, @dtsStartDate)
                END,
                111
            )
        )
        SELECT @insTerms = @insTerms + 1
    END

    RETURN
END

---------------------------------------------------------------------
-- 10.3 インラインテーブル値関数
---------------------------------------------------------------------

CREATE FUNCTION dbo.fn_DepartmentEquipment
(
    @chvUserName SYSNAME
)
RETURNS TABLE
AS
RETURN
(
    SELECT InventoryId, MAKE + ' ' + Model Model, Location
    FROM dbo.Inventory Inventory INNER JOIN dbo.Contact C
        ON Inventory.OwnerId = C.ContactId
    INNER JOIN dbo.Contact Manager
        ON C.OrgUnitId = Manager.OrgUnitId
    INNER JOIN dbo.Equipment Equipment
        ON Inventory.EqId = Equipment.EqId
    INNER JOIN dbo.Location Location
        ON Inventory.LocationId = Location.LocationId
    WHERE Manager.UserName = @chvUserName
)


SELECT *
FROM fn_DepartmentEquipment ('dejans')
GO

