100分求结转库存的存储过程

Question

--测试数据
--货品期初表
if exists(select * from goodsb) 
drop table goodsb create table GoodsB
(
id int identity(1,1) primary key,
goodsid int,
storeid int,
num float,
price float,
start datetime
)
insert into goodsb(goodsid,storeid,num,price,start)values(1,1,10,5,'06-8-1')
insert into goodsb(goodsid,storeid,num,price,start)values(1,2,3,1,'06-8-1' )
insert into goodsb(goodsid,storeid,num,price,start)values(2,1,4,5,'06-8-1' )
insert into goodsb(goodsid,storeid,num,price,start)values(3,1,6,2,'06-8-1' )
--入库表
if exists(select * from instore) drop table instorecreate table InStore
(
id int identity(1,1) primary key,
goodsid int,
storeid int,
num float,
price float,
start datetime
)
insert into instore(goodsid,storeid,num,price,start)values(1,1,10,0,'06-8-2')
insert into instore(goodsid,storeid,num,price,start)values(2,1,10,10,'06-8-2')
insert into instore(goodsid,storeid,num,price,start)values(3,2,2,5,'06-8-2')--出库表
if exists(select * from OUTstore) drop table OUTStore
create table outstore(
id int identity(1,1) primary key,
goodsid int,
storeid int,
num float,
start datetime
)INSERT INTO outstore(GOODSID,STOREID,NUM,START)VALUES( 1,1,10,'06-8-3' )
INSERT INTO OUTSTORE(GOODSID,STOREID,NUM,START)VALUES( 2,1,5,'06-8-3' )
INSERT INTO OUTSTORE(GOODSID,STOREID,NUM,START)VALUES( 1,2,3,'06-8-3' )
--求把下月期初(本月月末,全月一次平均法)数据插入GOODSB表的语句,例以上应插入数据为:
goodsid    storeid    num    price    start
1          1          10     2.5      '06-9-1'
1          2          0      0        '06-9-1' --数量为零的行可以省去
2          1          9      8.57142  '06-9-1'
3          1          6      2        '06-9-1'
3          2          2      5        '06-9-1'

Answer 1

这是到月底才一次性结转，需要触发器吗，SQL语句应该可以了吧

Answer 2

insert GOODSB
select goodsid,storeid,sum(num) as num,sum(price*num)/sum(num) as price,'06-9-1' as start
from (
select goodsid,storeid,num,price
from GOODSB
where start='06-8-1'
union all
select goodsid,storeid,num,price
from InStore
where start>='06-8-1'
and start<'06-9-1'
union all
select goodsid,storeid,-num,-price
from OutStore
where start>='06-8-1'
and start<'06-9-1'
) as t
group by goodsid,storeid

Answer 3

好像有点问题,OutStore没有Priceinsert GOODSB
select a.goodsid,a.storeid,a.num-b.num,a.price,'06-9-1' as start
from (
select goodsid,storeid,sum(num) as num,sum(price*num)/sum(num) as price
from (
select goodsid,storeid,num,price
from GOODSB
where start='06-8-1'
union all
select goodsid,storeid,num,price
from InStore
where start>='06-8-1'
and start<'06-9-1'
) as t
group by goodsid,storeid
) as a
left join (
select goodsid,storeid,sum(num) as num
from OutStore
where start>='06-8-1'
and start<'06-9-1'
group by goodsid,storeid
) as b
on a.goodsid=b.goodsid and a.storeid=b.storeid

Answer 4

这样试试:
INSERT INTO goodsB(goodsid,storeid,num,price,start)
SELECT * FROM
(
SELECT x.goodsid,x.storeid,
sum(isnull(g.num,0) + isnull(i.num,0) - isnull(o.num,0)) as num,
sum(isnull(g.num*g.price,0) + isnull(i.num*i.price,0))/sum(isnull(g.num,0) + isnull(i.num,0)) as price,
'06-9-1' as start
FROM 
(
select goodsid,storeid from goodsb where datediff(dd,start,'06-8-1') = 0
union
select goodsid,storeid from instore where year(start) = 2006 and month(start) = 8
union
select goodsid,storeid from outstore  where year(start) = 2006 and month(start) = 8
) x
left join (select * from goodsb where datediff(dd,start,'06-8-1') = 0) g 
on x.goodsid = g.goodsid and x.storeid = g.storeid
left join (select * from instore where year(start) = 2006 and month(start) = 8) i 
on x.goodsid = i.goodsid and x.storeid = i.storeid
left join (select * from outstore where year(start) = 2006 and month(start) = 8) o 
on x.goodsid = o.goodsid and x.storeid = o.storeid
GROUP BY x.goodsid,x.storeid 
) y 
WHERE num <> 0 ORDER BY goodsid

Answer 5

不要0的怎么又列出来？insert GOODSB
select a.goodsid,a.storeid,a.num-isnull(b.num,0) as num,a.price,'06-9-1' as start
from (
select goodsid,storeid,sum(num) as num,sum(price*num)/sum(num) as price
from (
select goodsid,storeid,num,price
from GOODSB
where start='06-8-1'
union all
select goodsid,storeid,num,price
from InStore
where start>='06-8-1'
and start<'06-9-1'
) as t
group by goodsid,storeid
) as a
left join (
select goodsid,storeid,sum(num) as num
from OutStore
where start>='06-8-1'
and start<'06-9-1'
group by goodsid,storeid
) as b
on a.goodsid=b.goodsid and a.storeid=b.storeid
where a.num-isnull(b.num,0)<>0