Share this post

The case :

We have a table with this data :

Now, we want to have packages of 100 letters. So for example, for the Center 1, we want 65 packages of 100 letters and one package of 4 letters.

The table with the data is TempData

1. The first solution is to use a cursor :

declare @mod int
Set @mod = 100 --The number of letters per package

declare @nb int,
	@start int,
	@end int,
	@tot int,
	@rest int,
	@cpt int,
	@centre nvarchar(200)

DECLARE @Tbl TABLE
(
  ID int IDENTITY(1,1) PRIMARY KEY,
  Centre nvarchar(200),
  NbStart int,
  NbEnd int,
  Total int,
  Remain int
)

declare curseur cursor for select Centre, NbTotal from TempData 
open curseur

fetch next from curseur into @centre, @nb
while @@fetch_status = 0
begin

	SET @tot = @nb
	SET @start = 1
	SET @end = case when @mod < @tot then @mod else @tot end
	SET @rest = case when @mod < @tot then @tot-@end else 0 end
	set @cpt = @rest % @mod
	
	if @cpt = 0
	begin
		Insert into @Tbl (Centre, NbStart, NbEnd, Total, Remain)
				select @centre, @start, @end, @tot, @rest
	end
	else
	begin
	while @cpt > 0
		BEGIN
		
			Insert into @Tbl (Centre, NbStart, NbEnd, Total, Remain)
				select @centre, @start, @end, @tot, @rest
			
			set @cpt = @rest % @mod
				
			SET @start = @end + 1
			SET @end = @end + @mod
			
			if @end > @tot 
			BEGIN
				SET @end = @tot
				SET @rest = 0
			END
			ELSE
				SET @rest = @tot-@end
		END
	end
	
fetch next from curseur into @centre, @nb

end

close curseur
deallocate curseur
	
select * from @Tbl

2. The most simple solution is to use the system table master..spt_values :

declare @mod int
Set @mod = 100 --The number of letters per package

if OBJECT_ID('tempdb..#temp') is not null drop table #temp

SELECT Centre, Nb, NbPage = Nb/@mod + case when Nb % @mod > 0 THEN 1 ELSE 0 END 
into #temp
from TempData  

SELECT  T.Centre, T.Nb, 
	ROW_NUMBER() over (PARTITION BY Centre ORDER BY Centre, NbPage) AS ParcelNumber, 
	NbPerParcel = CASE WHEN ROW_NUMBER() over (PARTITION BY Centre ORDER BY Centre, NbPage) * @mod > Nb THEN Nb % @mod ELSE @mod END
FROM    #temp t
CROSS APPLY
( SELECT  *
  FROM    master..spt_values m
  WHERE m.number <= T.NbPage AND m.number > 0 and m.Type = 'P'
) x

if OBJECT_ID('tempdb..#temp') is not null drop table #temp

The source used for this solution was there : http://www.sqlservercentral.com/Forums/Topic1040609-338-1.aspx