CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Masa Rumi
Masa Rumi

Reputation: 695

Partition by Quarterly Years

How can I add another column that would partition them quarterly (Jan-March, April-June, June-Sep) and then add another counter to keep track of the quarterly and define that Q1 2011 is not the same as Q1 2012. Essentially how would I add the Quarters and Tracker Column. I have looked at ROW_NUMBER(), NTILE functions but not sure how to combine them with months. 

--- Period --- Quarters---Tracker
2012-05-06       2          1
2012-05-20       2          1          
2012-06-03       2          1
2012-07-01       3          2
2012-08-12       3          2
2012-08-26       3          2
2012-09-09       3          2
2012-10-07       4          3
2012-10-21       4          3
2012-11-04       4          3
2012-11-18       4          3
2012-12-02       4          3
2012-12-16       4          3
2012-12-30       4          3 
2013-01-13       1          4
2013-01-27       1          4

REALLY STUCK!

Upvotes: 0

Views: 2614

Answers (2)

Eric J. Price
Eric J. Price

Reputation: 2785

I put the quarter CASE logic in the table definition, but you could also put it in the query so you don't have to modify your table.

Create  Table Blah 
(
        SampleDate Date Default(Convert(Date,Getdate())),
        Quarters As (   Case
                        When    Month(SampleDate) Between 1 And 3 Then 1
                        When    Month(SampleDate) Between 4 And 6 Then 2
                        When    Month(SampleDate) Between 7 And 9 Then 3
                        Else    4 End)
)

Insert  Blah (SampleDate)
Select  '2012-05-06'
Union   All
Select  '2012-05-20'
Union   All
Select  '2012-06-03'
Union   All
Select  '2012-07-01'
Union   All
Select  '2012-08-12'
Union   All
Select  '2012-09-09'
Union   All
Select  '2012-10-07'
Union   All
Select  '2012-11-04'
Union   All
Select  '2012-12-16'
Union   All
Select  '2013-01-13'
Union   All
Select  '2013-01-27'

Select  *, 
        Dense_Rank() Over (Order By Year(SampleDate),Quarters) As Tracker
From    Blah

Upvotes: 2

Rgwan
Rgwan

Reputation: 50

So you want a simple column to represent your actual quarter? 2012-Q1, 2011-Q1, 2010-Q1 that you would like to use SQL Partitions on? Or you want 2 columns? One to be partitioned on, and another one to actually indicate the year?

Thinking about it, do you need a counter? Couldn't you just set up the other column to be the year?

so you would have 2 columns. One indicating the quarter, and the other the year

quarter year
1       2011
1       2012
1       2010

Upvotes: 0

Related Questions