CODEWITHSUNDEEP
sql-server-2008-r2
Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Partitioned by Year

I have a year table like this. Every year has 12 values (Fixed)

declare @t table (FiscalYear int,[Month] varchar(25))
insert into @t values
(2011,'Jan'),(2011,'Feb'),(2011,'Mar'),(2011,'Apr'),
(2011,'May'),(2011,'Jun'),(2011,'Jul'),(2011,'Aug'),
(2011,'Sep'),(2011,'Oct'),(2011,'Nov'),(2011,'Dec'),
(2012,'Jan'),(2012,'Feb'),(2012,'Mar'),(2012,'Apr'),
(2012,'May'),(2012,'Jun'),(2012,'Jul'),(2012,'Aug'),
(2012,'Sep'),(2012,'Oct'),(2012,'Nov'),(2012,'Dec'),
(2013,'Jan'),(2013,'Feb'),(2013,'Mar'),(2013,'Apr'),
(2013,'May'),(2013,'Jun'),(2013,'Jul'),(2013,'Aug'),
(2013,'Sep'),(2013,'Oct'),(2013,'Nov'),(2013,'Dec')

I want to output as

FYear   Month   Qt  Qtp
2011    Jan     1   1
2011    Feb     1   2
2011    Mar     1   3
2011    Apr     2   1
2011    May     2   2
2011    Jun     2   3
2011    Jul     3   1
2011    Aug     3   2
2011    Sep     3   3
2011    Oct     4   1
2011    Nov     4   2
2011    Dec     4   3
2012    Jan     1   1
2012    Feb     1   2
2012    Mar     1   3
2012    Apr     2   1
2012    May     2   2
2012    Jun     2   3
2012    Jul     3   1
2012    Aug     3   2
2012    Sep     3   3
2012    Oct     4   1
2012    Nov     4   2
2012    Dec     4   3
2013    Jan     1   1
2013    Feb     1   2
2013    Mar     1   3
2013    Apr     2   1
2013    May     2   2
2013    Jun     2   3
2013    Jul     3   1
2013    Aug     3   2
2013    Sep     3   3
2013    Oct     4   1
2013    Nov     4   2
2013    Dec     4   3

How can i do that in SQLServer2008R2. I have tried using DenseRank, RowNuber, Partitioned but all in vain.

Upvotes: 2

Views: 305

Answers (2)

rufu5
rufu5

Reputation: 66

I propose dynamically populating a table with date values from Dec 2013 going down to the year that you like (you can alter the @COUNT_Y Variable to add more years).

SQL has some interesting datetime functions like DATEPART which can tell you which quarter a month is in etc.

** Answer changed due to question change **

    DECLARE @DATES TABLE
    (
        xDATE DATETIME
    )

    DECLARE @STARTDATE DATETIME = '12-31-2013'

    DECLARE @COUNT_X INT = 0
    DECLARE @COUNT_X_MAX INT = 11

    DECLARE @COUNT_Y INT = 0
    DECLARE @COUNT_Y_MAX INT = 2

    WHILE (@COUNT_Y <= @COUNT_Y_MAX)
    BEGIN

        SET @COUNT_X = 0

        WHILE (@COUNT_X <= @COUNT_X_MAX)
        BEGIN

            INSERT INTO @DATES
            SELECT DATEADD(MONTH, -@COUNT_X, DATEADD(YEAR,-@COUNT_Y, @STARTDATE))

            SET @COUNT_X = @COUNT_X + 1
        END

        SET @COUNT_Y = @COUNT_Y + 1
    END

    SELECT * FROM
    (SELECT
    DATEPART(YEAR, D.xDATE) AS [YEAR],
    DATEPART(MONTH, D.xDATE) AS [MONTH],
    DATENAME(MONTH, D.xDATE) AS [MONTH_NAME],
    DATEPART(QUARTER, D.xDATE) AS [QUARTER],
    DATEPART(MONTH, D.xDATE) - (3 * (DATEPART(QUARTER, D.xDATE) - 1)) AS [QTP]
    FROM @DATES D) t
    ORDER BY T.YEAR, T.MONTH

Upvotes: 1

aditya potdar
aditya potdar

Reputation: 357

Tru using Ntile:

--select * from @t      
SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY FYear, Qt ORDER BY FYear ) Qtp   
from
(SELECT FYear,[Month],
NTILE(4) OVER ( PARTITION BY FYear ORDER BY FYear ) AS Qt
FROM @t) PERIOD
ORDER BY FYear ,Qt ,ROW_NUMBER() OVER ( PARTITION BY FYear, Qt ORDER BY FYear)

Upvotes: 5

Related Questions