Assign 1 to the first 12 rows, 2 to the 2nd 12 etc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Assign 1 to the first 12 rows, 2 to the 2nd 12 etc

Author

Topic

stoolpidgeon
Starting Member

28 Posts

Posted - 2014-07-13 : 07:00:42

I have a query currently returning dates in the first column and sum(spend) in the second.

I want to assign the number 1 to the first 12 records, 2 to the second 12 records etc until there's no rows left. This is ultimately to be able to partition over these numbers so I can work out the cumulative spend over the year using sum(spend) over (partition by.. ).
The 12 months aren't necessarily jan-dec so I can't partition over year.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-13 : 08:05:58

[code]SELECT *,
1 + (ROW_NUMBER() OVER (PARTITON BY theYear ORDER BY theDate) - 1) / 12
FROM YourSource;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

stoolpidgeon
Starting Member

28 Posts

Posted - 2014-07-13 : 11:53:55

That's just created a column with 1's. I want 1's for the first 12 entries, 2's for the second 12 entries and so on

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-13 : 12:07:49

It's not possible to get all 1's. You must have altered the query in some way.

SELECT name,
       1 + (ROW_NUMBER() OVER (PARTITION BY xtype ORDER BY id) - 1) / 12 AS SwePeso
FROM   sysobjects
WHERE	xtype = 'S';

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

stoolpidgeon
Starting Member

28 Posts

Posted - 2014-07-13 : 12:55:12

Apologies, I lost a set of parenthesis.

Great soln - thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources