Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE PROCEDURE getGroups
- (
- @groupByQty INT, -- grouping number
- @numberRuns INT -- how many loops
- -- usage: getGroups 10, 10
- )
- AS
- 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;
- SELECT * FROM #TBL_ALL;
- DROP TABLE #TBL_COUNT;
- DROP TABLE #TBL_ALL;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement