[SQL Server] item packing

-- Item packing
/*

Http://topic.csdn.net/u/20100703/16/bcc2efaf-5aee-424d-b022-473282a168ae.html? Seed = 657778656 & R = 66694963 # r_66694963

There is a table with the following fields: Item Name and number of items.
Record: Things a 54
Thing B 35
Things C 23
Things D 98
Things E 43
Now we need to pack these items in a uniform manner, with 60 items in one box. Then we need to pack 54 items in the first case of things a, 6 items in the second case of things B, and 29 items in the second case, there are 23 things in C, 8 things in D, and so on.
How can this problem be solved?

*/

If object_id ('[TB]') is not null drop table [TB]
Go
Create Table [TB] (item name varchar (10), number of pieces INT)
Insert [TB]
Select 'thing A', 54 Union all
Select 'thing B ', 35 Union all
Select 'thing C', 23 Union all
Select 'thing d', 98 Union all
Select 'thing E', 43
Go
Set nocount on
Declare @ item varchar (10), @ count int, @ box number int, @ remaining int
Declare @ t table (box number int, item varchar (10), number INT)
Declare cur cursor for select * from TB
Open cur
Fetch cur into @ item, @ quantity
Set @ box number = 1
While @ fetch_status = 0
Begin
While 1 = 1
Begin
Select @ remainder = isnull (sum (Quantity), 0) from @ t where box number = @ box number
If @ count> 60-@ remaining
Begin
Set @ quantity = @ quantity-(60-@ remaining)
Insert @ t select @ box number, @ item, 60-@ remaining
Set @ box number = @ box number + 1
End
Else
Begin
Insert @ t select @ box number, @ item, @ quantity
Break
End
End
Fetch cur into @ item, @ quantity
End
Close cur
Deallocate cur

Select * From @ t

 

/*
Number of items in the box No.
--------------------------------
1 things a 54
1 thing B 6
2 things B 29
2 things C 23
2 things d 8
3 things D 60
4 things d 30
4 things E 30
5 things E 13
*/