Labels

Wednesday, April 3, 2013

Get Last 12 Months and their Start Dates

CREATE PROCEDURE P_Last12Months
AS
BEGIN
    DECLARE @Months TABLE
    (
    MID INT IDENTITY(1,1),
    MonthNumber INT,
    MonthDesc VARCHAR(15),
    StartDate DATE
    )

    DECLARE @Loop INT = 0, @Today DATETIME = GETDATE()

    WHILE (@Loop > -12)
    BEGIN

    INSERT @Months
    SELECT      IIF(MONTH(@Today) + @Loop <= 0 , 12 + MONTH(@Today) + @Loop, MONTH(@Today) + @Loop) AS MonthNumber
            , DATENAME(MONTH,DATEADD(MONTH, @Loop, @Today)) AS MonthDesc
            , DATEFROMPARTS(IIF(MONTH(@Today) + @Loop <= 0, YEAR(@Today) - 1,YEAR(@Today)),IIF(MONTH(@Today) + @Loop <= 0 , 12 + MONTH(@Today) + @Loop, MONTH(@Today) + @Loop) , 1)

    SET @Loop = @Loop - 1

    END

    SELECT * FROM @Months
END

No comments:

Post a Comment