表invtory结构如下
物料号 会计期 期初数 结存数
itemno accper bgnqty proqty
100 201301 0 10
110 201301 0 10
110 201302 0 5
110 201303 0 6
现在要通过一个存储过程，或者sql语句，将201301会计期结转到201302
要求得到如下结果
物料号 会计期 期初数 结存数
itemno accper bgnqty proqty
100 201301 0 10
100 201302 10 10
110 201301 0 10
110 201302 10 15
110 201303 0 6
劳驾，先谢过各位。
------解决方案--------------------
select itemno , accper , bgnqty , proqty from invtory
union all
select itemno , convert(int,accper)+1 , bgnqty , proqty from invtory where accper =' 201301'
------解决方案--------------------
select * into #t from (
select 100 itemno,'201301' accper,0 bgnqty, 10 proqty
union all select 110, '201301', 0, 10
union all select 110, '201302', 0, 5
union all select 110, '201303', 0, 6
)a
select itemno,accper,sum(bgnqty)bgnqty,sum(proqty)proqty from (
select * from #t
union all
select itemno,'201302'accper,proqty as bgnqty,proqty from #t where accper='201301'
)a
group by itemno,accper
order by itemno,accper
/*
itemno accper bgnqty proqty
100 201301 0 10
100 201302 10 10
110 201301 0 10
110 201302 10 15
110 201303 0 6
*/
------解决方案--------------------
你这个得分两步了，插入一步，更新一步
;with cte as (
select itemno,accper,sum(bgnqty)bgnqty,sum(proqty)proqty from (