求一结转SQL
表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
/*
itemnoaccperbgnqtyproqty
100201301010
1002013021010
110201301010
1102013021015
11020130306
*/
;with cte as (
select itemno,accper,sum(bgnqty)bgnqty,sum(proqty)proqty from (
select * from #t
union all
select itemno,left(accper,4)+'02' accper,proqty as bgnqty,proqty from #t where right(accper,2)='01'
)a
group by itemno,accper
)update a set a.bgnqty=b.bgnqty,a.proqty=b.proqty from
#t a inner join cte b on a.itemno=b.itemno and a.accper=b.accper
where right(a.accper,2)='02'
;with cte as (
select itemno,accper,sum(bgnqty)bgnqty,sum(proqty)proqty from (
select * from #t
union all
select itemno,left(accper,4)+'02' accper,proqty as bgnqty,proqty from #t where right(accper,2)='01'
)a
group by itemno,accper
)insert into #t(itemno,accper,bgnqty,proqty)
select * from cte a where not exists(select 1 from #t b where a.itemno=b.itemno and a.accper=b.accper)
if OBJECT_ID('test') is not null
drop table test
go
create table test(itemno int,accper varchar(8),bgnqty int,proqty int)
insert into test
select 100, '201301', 0, 10 union all
select 110, '201301', 0, 10 union all
select 110, '201302', 0, 5 union all
select 110, '201303', 0, 6
go
update a set a.bgnqty=b.proqty+a.bgnqty
from test a join test b on a.itemno=b.itemno and a.accper='201302' and b.accper='201301'
update test set proqty=proqty+bgnqty
insert into test
select itemno,'201302',proqty,proqty from test t where not exists(
select 1 from test where itemno=t.itemno and accper='201302'
)
select * from test order by itemno
/*
(1 row(s) affected)
itemno accper bgnqty proqty
----------- -------- ----------- -----------
100 201301 0 10
100 201302 10 10
110 201301 0 10
110 201302 10 15
110 201303 0 6
*/
