如何按照条件插入数据，使得累计值大于一定值？
原表：
week qty Item
1 100 1
1 50 1
1 -300 2
2 -500 2
2 -100 2
2 -300 2
2 150 1

分析：
week qty Item
1 100 1
1 50 1
1 -300 2

week   1   累计值   -150
想要保持week   1累计值为   100
插入一条纪录
week qty Item
1 250 3

插入此条纪录后
week   1   和   week   2的累计值为   -650

week qty Item
1 100 1
1 50 1
1 -300 2
1 250 3
2 -500 2
2 -100 2
2 -300 2
2 150 1

在week2中插入一条纪录
使得week   1   和   week   2的累计值也为100
week qty Item
2 750 3

最终插入两条纪录的结果表：
week qty Item
1 100 1
1 50 1
1 -300 2
1 250 3
2 -500 2
2 -100 2
2 -300 2
2 150 1
2 750 3
请问如何建立存储过程，自动插入Item   3的那两条纪录？



------解决方案--------------------
drop table weektest
go
create table weektest(week int,qty int,item int)
go
insert into weektest
select 1,100,1
union all select 1,50,1
union all select 1,-300,2
union all select 2,-500,2
union all select 2,-100,2
union all select 2,-300,2
union all select 2,150,1
go

create proc up_week
as
declare @maxweek int
select @maxweek = max(week) from weektest
declare @week int
declare @qty int
declare @item int
declare cur_tmp cursor for
select distinct week from weektest
open cur_tmp
fetch next from cur_tmp into @week
while @@fetch_status=0
begin
if @week <> @maxweek
begin
select @qty = sum(qty) from weektest where week=@week
if @qty <> 100
begin
select @item=max(item)+1 from weektest where week=@week
insert into weektest(week,qty,item)
select @week,100 - @qty,@item
end
end
else
begin
select @qty = sum(qty) from weektest
if @qty <> 100
begin
select @item=max(item)+1 from weektest where week=@maxweek
insert into weektest(week,qty,item)
select @week,100 - @qty,@item
end
end
fetch next from cur_tmp into @week
end
close cur_tmp
deallocate cur_tmp
go

select * from weektest order by week

/*
week qty item
----------- ----------- -----------
1 100 1
1 50 1
1 -300 2
1 250 3
2 150 1
2 -100 2
2 -300 2
2 -500 2
2 750 3

（所影响的行数为 9 行）
*/
------解决方案--------------------
主要是通过自定义函数。
create table t(week int, qty int, Item int)
insert t select 1, 100, 1
union all select 1, 50, 1
union all select 1, -300, 2
union all select 2, -500, 2
union all select 2, -100, 2
union all select 2, -300, 2
union all select 2, 150, 1
GO

CREATE function f_table(@week int)
returns @t table(week int, qty int, item int)
as
begin
declare @qty int
select @qty = sum(qty) from t group by week having week = @week
if @qty < 100
begin
insert into @t select @week, 100 - @qty, 3
end
return
end
Go

自己写动态语句来循环每一周。
insert into t
select dbo.f_table(1)

drop table t
drop function f_table


------解