CODEWITHSUNDEEP
sql-serversql-server-2008t-sqldatetimessms
Lebowski
Lebowski

Reputation: 121

SQL previous 12 months function

This time i need a function for just picking the previous 12 months. If you pick December 2013, it should return data from November 2013 and to November 2012. The result should show: 

 - Year   -      Month
 - 2013   -       11
 - 2013   -       10
 - 2013   -       09

My previous question got answered, but i dont really know how to edit the date part to just pick months:

    ALTER function [dbo].[LastMonths]
    (
        @Date datetime
    )   RETURNS @tbl TABLE (Start datetime, EndDate datetime)
    AS   
    BEGIN
        WITH T AS(
        SELECT 
            DATEADD(month, DATEDIFF(month, 0, @Date), 0) AS Start,
            DATEADD(d, -DAY(DATEADD(m,1,@date)),DATEADD(m,1,@date)) AS EndDate,
            12 Cnt
        UNION ALL
        SELECT 
            DATEADD(month, -1, Start),
            DATEADD(d, -DAY(DATEADD(m,1,Start-1)),DATEADD(m,1,Start-1)),
            Cnt-1
        FROM
            T
        WHERE
            Cnt-1>0
        )
        INSERT INTO @tbl 
            (Start, EndDate)
        SELECT 
            Start, EndDate
        FROM T

RETURN
    END

Thanks for everyone who is so kind to help!

Upvotes: 0

Views: 2431

Answers (3)

Anon
Anon

Reputation: 10908

CREATE FUNCTION [dbo].[PrevMonths] ( 
    @date datetime
)
RETURNS TABLE
RETURN (
    SELECT
      YEAR( DATEADD(month,delta,@date)) AS [Year],
      MONTH(DATEADD(month,delta,@date)) AS [Month]
    FROM (VALUES (-1),(-2),(-3),(-4),(-5),(-6),(-7),(-8),(-9),(-10),(-11),(-12)) T(delta)
)

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

try this:

create function [dbo].[LastMonths]
    (
        @Date datetime
    )   RETURNS @tbl TABLE (year int, month int)
    AS   
    BEGIN
        WITH T AS(
        SELECT 
            dateadd(m, -1, @date) d
        UNION ALL
        SELECT 
            dateadd(m, -1, d) d
        FROM
            T
        WHERE
            d > dateadd(m, -13, @date)
        )
        INSERT INTO @tbl 
        SELECT 
            year(d), month(d)
        FROM T

RETURN
END

Testing:

select * from [LastMonths](getdate())

Result:

year    month
2013    11
2013    10
2013    9
2013    8
2013    7
2013    6
2013    5
2013    4
2013    3
2013    2
2013    1
2012    12
2012    11

Upvotes: 1

Ric
Ric

Reputation: 13248

I modified the query slightly to the following:

declare @date date = '01 december 2013'

;WITH T AS(
SELECT 
    DATEADD(month, -1, @date) AS Start,
    12 Cnt
    UNION ALL
SELECT 
     DATEADD(month, -1, Start),
     Cnt-1
FROM T
WHERE Cnt-1>=0
)
SELECT  YEAR(start) [year], MONTH(Start) [month]
FROM    t

the following results were produced:

year    month
2013    11
2013    10
2013    9
2013    8
2013    7
2013    6
2013    5
2013    4
2013    3
2013    2
2013    1
2012    12
2012    11

This seems to produce the results you wanted, but if this is not the case then feel free to comment.

Upvotes: 1

Related Questions