执行前备份数据
(请在查询管理器中运行,然后反结账到期初的月再结账到到当前月,这样库存就对了
程序中的这个是开账年月要改一下根据自己的posyear=2009 and posmonth=1 )
declare @goodsid int, @storeid int,@sumqty int
declare cur_calc cursor local forward_only static read_only for
select goodsid,storeid,sum(quantity) as sumqty from aa_billflow where billid<0 group by goodsid,storeid
open cur_calc
fetch next from cur_calc into @goodsid,@storeid,@sumqty
while @@fetch_status = 0
begin
update aa_storebalance
set bqty=isnull(@sumqty,0), brqty=isnull(@sumqty,0)
where [email=storeid=@storeid]storeid=@storeid[/email] and [email=goodsid=@goodsid]goodsid=@goodsid[/email] and posyear=2009 and posmonth=1 --and bqty<>brqty
fetch next from cur_calc into @goodsid,@storeid,@sumqty
end
close cur_calc
deallocate cur_calc
update aa_storebalance
set bqty=0, brqty=0
where not exists (select * from aa_billflow
where aa_storebalance.storeid=aa_billflow.storeid and aa_storebalance.goodsid=aa_billflow.goodsid and aa_billflow.billid<0)
and aa_storebalance.bqty<>0
[ 本帖最后由 cyjxj 于 2009-3-5 12:16 编辑 ] |