question

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