Hi kishore21,
I have provided a sample procedure. So you need to modify as per your requirement.
When you are dealing with such task in SQL you need to go to the cursors approach.
Cursors let you perform row by row operations and that's what you need.
SQL
CREATE TABLE tblExample(Id INT IDENTITY,Name VARCHAR(10),Qty INT)
INSERT INTO tblExample VALUES('john',4000)
INSERT INTO tblExample VALUES('albert',5000)
INSERT INTO tblExample VALUES('cena',2000)
INSERT INTO tblExample VALUES('randy',3000)
SELECT * FROM tblExample
--EXEC getGroups 8000
CREATE PROCEDURE getGroups
(
@groupByQty int,
@numberRuns int = 10
)
AS
BEGIN
SET NOCOUNT ON;
-- declare all variables
DECLARE @rowId int,
@rowQty int,
@rowTotal int,
@groupId int,
@totalRuns int,
@continue bit
-- set up our final temporary table
CREATE TABLE #TBL_COUNT
(
ids NVARCHAR(4000),
qty int,
[group] int
)
-- initializate variable
SET @groupId = 1;
SET @continue = 1;
SET @totalRuns = 0;
SELECT Id, Qty INTO #TBL_ALL FROM tblExample ORDER BY Qty DESC;
WHILE @totalRuns <= @numberRuns
BEGIN
-- declare the cursor
DECLARE Product CURSOR FOR SELECT Id, Qty FROM #TBL_ALL ORDER BY Qty DESC;
OPEN Product;
FETCH Product INTO @rowId, @rowQty;
PRINT ' ';
PRINT '### Run: ' + CAST(@totalRuns AS nvarchar(10)) + ' #################################################################';
PRINT 'Grouping Table by ' + CAST(@groupByQty AS nvarchar(10)) + ' | group id = ' + CAST(@groupId AS nvarchar(10));
-- Retrieve and process the first row
SELECT Top 1 @rowId = Id, @rowQty = Qty FROM #TBL_ALL ORDER BY Qty DESC;
PRINT 'First Row: id = ' + CAST(@rowId AS nvarchar(10)) + ' | qty = ' + CAST(@rowQty AS nvarchar(10));
-- sum it up and see if we have @groupByQty
SELECT @rowTotal = ISNULL(SUM(qty),0) FROM #TBL_COUNT WHERE [group] = @groupId;
PRINT 'Current sum in #TBL_COUNT: @groupId = '+ CAST(@groupId AS nvarchar(10)) +' | @rowTotal = ' + CAST(@rowTotal AS nvarchar(10)) + ' | (@rowTotal + @rowQty) = ' + CAST((@rowTotal + @rowQty) AS nvarchar(10));
IF @rowQty > @groupByQty
BEGIN
PRINT ' x First row has an unused number';
END
ELSE
BEGIN
-- handle result
IF (@rowTotal + @rowQty) = @groupByQty
BEGIN
PRINT '+++ Current sum is ' + CAST(@groupByQty AS nvarchar(10)) + ' +++';
-- save number
INSERT INTO #TBL_COUNT SELECT @rowId, @rowQty, @groupId;
PRINT '### Inserted final # into #TBL_COUNT : id = ' + CAST(@rowId AS nvarchar(10)) + ' | qty = ' + CAST(@rowQty AS nvarchar(10)) + ' | group = ' + CAST(@groupId AS nvarchar(10));
-- remove from table as we use it already
DELETE FROM #TBL_ALL WHERE Id = @rowId;
-- we got 10, let's change our Groupping
SET @groupId = (@groupId + 1);
PRINT 'New group id: ' + CAST(@groupId AS nvarchar(10));
END
ELSE
BEGIN
IF (@rowTotal + @rowQty) < @groupByQty
BEGIN
PRINT '### Inserted into #TBL_COUNT : id = ' + CAST(@rowId AS nvarchar(10)) + ' | qty = ' + CAST(@rowQty AS nvarchar(10)) + ' | group = ' + CAST(@groupId AS nvarchar(10));
-- save number
INSERT INTO #TBL_COUNT SELECT @rowId, @rowQty, @groupId;
-- remove from table as we use it already
DELETE FROM #TBL_ALL WHERE Id = @rowId;
END
ELSE
BEGIN
PRINT ' x Unmatch number, will handle this latter';
END
END
END
-- start the main processing loop
WHILE @@Fetch_Status = 0
BEGIN
FETCH Product INTO @rowId, @rowQty;
PRINT '@@Fetch_Status = ' + CAST(@@Fetch_Status AS nvarchar(100));
IF @@Fetch_Status < 0
BEGIN
BREAK
END
-- we have the values of our row, let's use them
PRINT 'Fetched Row: id = ' + CAST(@rowId AS nvarchar(10)) + ' | qty = ' + CAST(@rowQty AS nvarchar(10));
-- sum it up and see if we have @groupByQty
SELECT @rowTotal = ISNULL(SUM(qty),0) FROM #TBL_COUNT WHERE [group] = @groupId;
PRINT 'Current sum in #TBL_COUNT: @groupId = '+ CAST(@groupId AS nvarchar(10)) +' | @rowTotal = ' + CAST(@rowTotal AS nvarchar(10)) + ' | (@rowTotal + @rowQty) = ' + CAST((@rowTotal + @rowQty) AS nvarchar(10));
-- handle result
IF (@rowTotal + @rowQty) = @groupByQty
BEGIN
PRINT '+++ Current sum is ' + CAST(@groupByQty AS nvarchar(10)) + ' +++';
-- save number
INSERT INTO #TBL_COUNT SELECT @rowId, @rowQty, @groupId;
PRINT '### Inserted final # into #TBL_COUNT : id = ' + CAST(@rowId AS nvarchar(10)) + ' | qty = ' + CAST(@rowQty AS nvarchar(10)) + ' | group = ' + CAST(@groupId AS nvarchar(10));
-- remove from table as we use it already
DELETE FROM #TBL_ALL WHERE Id = @rowId;
-- we got 10, let's change our Groupping
SET @groupId = (@groupId + 1);
PRINT 'New group id: ' + CAST(@groupId AS nvarchar(10));
-- start again
BREAK;
END
ELSE
BEGIN
IF (@rowTotal + @rowQty) < @groupByQty
BEGIN
PRINT '### Inserted into #TBL_COUNT : id = ' + CAST(@rowId AS nvarchar(10)) + ' | qty = ' + CAST(@rowQty AS nvarchar(10)) + ' | group = ' + CAST(@groupId AS nvarchar(10));
-- save number
INSERT INTO #TBL_COUNT SELECT @rowId, @rowQty, @groupId;
-- remove from table as we use it already
DELETE FROM #TBL_ALL WHERE Id = @rowId;
END
ELSE
BEGIN
PRINT ' x Unmatch number, will handle this latter';
END
END
END -- END WHILE @@Fetch_Status = 0
SET @totalRuns = @totalRuns + 1;
-- Close and dealocate
CLOSE Product;
DEALLOCATE Product;
END -- END WHILE totalRuns <= @numberRuns
-- let's sum our last group and remove it if it's not @groupByQty
SELECT @rowTotal = ISNULL(SUM(qty),0) FROM #TBL_COUNT WHERE [group] = @groupId;
IF @rowTotal <> @groupByQty
BEGIN
SET IDENTITY_INSERT #TBL_ALL ON
INSERT INTO #TBL_ALL (Id, Qty) SELECT Ids, Qty FROM #TBL_COUNT WHERE [group] = @groupId;
DELETE FROM #TBL_COUNT WHERE [group] = @groupId;
END
SET NOCOUNT OFF;
-- Show and Delete temp tables
SELECT * FROM #TBL_COUNT WHERE [group] = 1;
--SELECT * FROM #TBL_ALL;
DROP TABLE #TBL_COUNT;
DROP TABLE #TBL_ALL;
END