question

aRookieBIdev avatar image
aRookieBIdev asked

SQL Groupby and Rownumber by sum

Hi All, I need your suggestion with the following scenario , I am currently trying with temp tables and rownumbers to get the desired output . CREATE TABLE [dbo].[Batch]( [BatchId] [int] NOT NULL, [DocumentId] [int] NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Document]( [DocumentId] [int] NULL, [NumberOfPages] [int] NULL ) ON [PRIMARY] INSERT [dbo].[Document] ([DocumentId], [NumberOfPages]) VALUES (1, 32) INSERT [dbo].[Document] ([DocumentId], [NumberOfPages]) VALUES (2, 25) INSERT [dbo].[Document] ([DocumentId], [NumberOfPages]) VALUES (3, 41) INSERT [dbo].[Document] ([DocumentId], [NumberOfPages]) VALUES (4, 82) INSERT [dbo].[Document] ([DocumentId], [NumberOfPages]) VALUES (5, 19) INSERT [dbo].[Document] ([DocumentId], [NumberOfPages]) VALUES (6, 81) /* I need to group the documents into a batches so that theie total page count is less than 100. example Batch 1 will have DocumentId 1 , 2, 3 which has a total page count of 32+25+41 = 98 Batch 2 will have DocumentId 4 which has a page count of 82 Batch 3 will have Document 5 and 6 which has a page count of 19+81 = 100 expected result is in Select * from Batch Note Document Id 4 and 5 can not be grouped because their page count exceeds 100 */ My Solution below : truncate table [dbo].[Batch] Declare @TempSum int Declare @DocumentId int Declare @NumberOfPages int Declare @BatchId int Set @TempSum = 0 ; Set @BatchId = 1; DECLARE db_cursor CURSOR FOR select DocumentId , NumberOfPages from [dbo].[Document] OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DocumentId , @NumberOfPages WHILE @@FETCH_STATUS = 0 BEGIN if ( (@TempSum + @NumberOfPages) ]]]]]]]]]] sql-server-2012group-bysum
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Isn't this the bin packing problem? Hugo Kornelis did a series of posts about it... http://sqlblog.com/blogs/hugo_kornelis/archive/tags/Bin+Packing/default.aspx
2 Likes 2 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Similar, but not looking for the efficiency of the lowest number of bins. I suspect the answer will be some adaptation of one of the methods Hugo presents.
2 Likes 2 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
First bin first / first fit method? https://www.simple-talk.com/sql/t-sql-programming/bin-packing-problems-the-sql/ - there are a few typos in this article, though, so go carefully through it...
2 Likes 2 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Is it important to batch items contiguously? Also is it important to batch them the most efficient way (fewest batches). Could a possible solution be: Batch 1 (Docs 1&2) = 32+25 = 57 Batch 2 (Docs 3&5) = 41+19 = 60 Batch 3 (Doc 4) = 82 Batch 4 (Doc 6) = 81 In your example it just so happens that when you keep the documents in the right order, you get an efficient batching.
0 Likes 0 ·
aRookieBIdev avatar image aRookieBIdev commented ·
Thats true.. unfortunately the batches i need should be contiguous. The method i used is not great ... i am looking for a better efficient way because I need to deal with a huge volume of data.
0 Likes 0 ·
Show more comments

1 Answer

·
alemgeb avatar image
alemgeb answered
Not quite sure if I did understand your question but from what I read select ff.BatchId,count(ff.DocumentId) as Number_of_DocumentID,sum(gg.NumberOfPages) as NumberOfPages from [dbo].[Batch] as ff join [dbo].[Document] as gg on ff.DocumentId=gg.DocumentId group by ff.BatchId
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Batch table is only provided to show what the OP wants the result to be - you can't use it as part of the solution
1 Like 1 ·
aRookieBIdev avatar image aRookieBIdev commented ·
Thats right ..I was trying to get that data in Batch table. To avoid any further confusion , I have removed those now.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.