Need a set based solution to group rows
- by KM
I need to group a set of rows based on the Category column, and also limit the combined rows based on the SUM(Number) column to be less than or equal to the @Limit value.
For each distinct Category column I need to identify "buckets" that are <=@limit. If the SUM(Number) of all the rows for a Category column are <=@Limit then there will be only 1 bucket for that Category value (like 'CCCC' in the sample data). However if the SUM(Number)@limit, then there will be multiple bucket rows for that Category value (like 'AAAA' in the sample data), and each bucket must be <=@Limit. There can be as many buckets as necessary. Also, look at Category value 'DDDD', its one row is greater than @Limit all by itself, and gets split into two rows in the result set.
Given this simplified data:
DECLARE @Detail table (DetailID int primary key, Category char(4), Number int)
SET NOCOUNT ON
INSERT @Detail VALUES ( 1, 'AAAA',100)
INSERT @Detail VALUES ( 2, 'AAAA', 50)
INSERT @Detail VALUES ( 3, 'AAAA',300)
INSERT @Detail VALUES ( 4, 'AAAA',200)
INSERT @Detail VALUES ( 5, 'BBBB',500)
INSERT @Detail VALUES ( 6, 'CCCC',200)
INSERT @Detail VALUES ( 7, 'CCCC',100)
INSERT @Detail VALUES ( 8, 'CCCC', 50)
INSERT @Detail VALUES ( 9, 'DDDD',800)
INSERT @Detail VALUES (10, 'EEEE',100)
SET NOCOUNT OFF
DECLARE @Limit int
SET @Limit=500
I need one of these result set:
DetailID Bucket | DetailID Category Bucket
-------- ------ | -------- -------- ------
1 1 | 1 'AAAA' 1
2 1 | 2 'AAAA' 1
3 1 | 3 'AAAA' 1
4 2 | 4 'AAAA' 2
5 3 OR 5 'BBBB' 1
6 4 | 6 'CCCC' 1
7 4 | 7 'CCCC' 1
8 4 | 8 'CCCC' 1
9 5 | 9 'DDDD' 1
9 6 | 9 'DDDD' 2
10 7 | 10 'EEEE' 1
