CODEWITHSUNDEEP
sql-servert-sql
Masa Rumi
Masa Rumi

Reputation: 695

Counter Column; Divide rows in table by nrows

I am struggling with the following:

Counter --- Period ---
 1         2012-02-09 
 1         2012-02-09 
 1         2012-02-08 
 2         2012-02-07 
 2         2012-02-07
 2         2012-02-07 
 3         2012-02-06
 3         2012-02-06

I don't know what function to use or how to add a counter column that will divide the period rows in the table by 3 and add a counts. It will divide until it can and assign the leftover rows as the next counter (as shown above). In the example above @n is 3 so it counts each period assigns 1 until 3 are complete and counters.

I have looked at NTILE that does not work as it just divides it into n groups.

Help will be greatly appreciated.

Upvotes: 2

Views: 299

Answers (2)

dugas
dugas

Reputation: 12463

Are you looking for something like:

declare @n as int = 3

SELECT
((ROW_NUMBER() over (order by period desc) - 1) / @n) + 1 as counter,
[period]
FROM [a].[dbo].[a]

Upvotes: 1

Geoff
Geoff

Reputation: 8850

It's possible you need to clarify your question; if I use NTILE() I get the result you're looking for (if you include an ID):

declare @tableA table(id int identity, col1 date)

insert into @tableA values ('2012-02-09')
insert into @tableA values ('2012-02-09')
insert into @tableA values ('2012-02-08')
insert into @tableA values ('2012-02-07')
insert into @tableA values ('2012-02-07')
insert into @tableA values ('2012-02-07')
insert into @tableA values ('2012-02-06')
insert into @tableA values ('2012-02-06')

select ntile(3) over (order by id) counter, col1 Period from @tableA

Results:

counter              Period
-------------------- ----------
1                    2012-02-09
1                    2012-02-09
1                    2012-02-08
2                    2012-02-07
2                    2012-02-07
2                    2012-02-07
3                    2012-02-06
3                    2012-02-06

Upvotes: 1

Related Questions