sp求解库存加权平均单价问题
火龙岛主 2006-05-18 09:56:29
表及其数据
create table crkmx(
bm varchar(16), --出入库编码
rq datetime, --日期
hp varchar(16), --货品
rkdj money, --入库单价
rkds money, --入库吨数
ckds money --出库吨数
)
bm rq hp rkdj rkds ckds
RK200605170001 2006-05-17 11:39:12.000 040002 100.0000000 5.0000 .000000
RK200605170002 2006-05-17 11:40:01.000 040002 200.0000000 10.0000 .000000
RK200605170003 2006-05-17 11:40:32.000 040002 500.0000000 8.0000 .000000
RK200605170004 2006-05-17 11:41:11.000 040002 800.0000000 2.0000 .000000
XS200605170001 2006-05-17 11:41:41.000 040002 .0000000000 0.0000 5.000000
XS200605170002 2006-05-17 11:41:54.000 040002 .0000000000 0.0000 5.000000
XS200605170003 2006-05-17 11:42:06.000 040002 .0000000000 0.0000 2.000000
RK200605170005 2006-05-17 11:44:12.000 040002 266.6666666 3.0000 .000000
XS200605170004 2006-05-17 11:45:54.000 040002 .0000000000 0.0000 5.000000
说明:如果有出库就可定没有入库的数值,也就是说出入库不存在对应关系
需要使用加权平均的方法得到货品的当前库存金额
加权单价(ckdj)=期初货款金额+入库金额/(期初数量+入库数量)
目标如下:
ckdj:出库单价, kcje:库存金额 kcds:库存吨数 qcje:期初金额
要求:使用视图或存储过程实现,不使用触发器和游标,以时序为准!
bm rq hp rkdj rkds ckds ckdj kcds kcje qcje
RK200605170001 2006-05-17 11:39:12.000 040002 100.0000000 5.0000 .000000 100.00 5.00 500.0000 0.0000
RK200605170002 2006-05-17 11:40:01.000 040002 200.0000000 10.0000 .000000 166.6667 15.00 2500.0000 500.0000
RK200605170003 2006-05-17 11:40:32.000 040002 500.0000000 8.0000 .000000 282.6087 23.00 6500.0000 2500.0000
RK200605170004 2006-05-17 11:41:11.000 040002 800.0000000 2.0000 .000000 324.0000 25.00 8100.0000 6500.0000
XS200605170001 2006-05-17 11:41:41.000 040002 .0000000000 0.0000 5.000000 324.0000 20.00 6480.0000 8100.0000
XS200605170002 2006-05-17 11:41:54.000 040002 .0000000000 0.0000 5.000000 324.0000 15.00 4680.0000 6480.0000
XS200605170003 2006-05-17 11:42:06.000 040002 .0000000000 0.0000 2.000000 324.0000 13.00 4212.0000 4680.0000
RK200605170005 2006-05-17 11:44:12.000 040002 266.6666667 3.0000 .000000 313.2499 16.00 5011.9998 4212.0000
XS200605170004 2006-05-17 11:45:54.000 040002 .0000000000 0.0000 5.000000 313.2499 11.00 3445.7489 5011.9998