SQLTeam.com | Weblogs | Forums

Chunking based on summation of column values

Hi,

I have a table which looks like:
Id, Col1, Col2
1, X, 200
2, Y, 300
3, Z, 500
4, A, 150
5, B, 450

I want to achieve grouping/chunking based on a passed value and the sum of values equal or less than in col2 across rows.
For ex: if pass value is 600 the chunking should be like below:
Id, Col1, Col2, chunk
1, X, 200, 1
2, Y, 300, 1
3, Z, 500, 2
4, A, 150, 3
5, B, 450, 3

create table #test
(
id int,
col1 varchar(20),
col2 int
)
insert into #test(id, col1, col2)
values
(1, 'X', 200),
(2, 'Y', 300),
(3, 'Z', 500),
(4, 'A', 150),
(5, 'B', 450)

SELECT
id,
col1,
col2,
SUM(col2) Over (Order by id) as runningTotal,
SUM(col2) Over (Order by id) % 600 AS remaining,
(SUM(col2) Over (Order by id) / 600) + 1 AS chunck
FROM #test