记录一下最近弄的一个成本核算的SQL算法
最近一个以前的朋友介绍,帮他的一个客户的ERP搞了一套SQL的成本核算方法,简单记录一下,后面方便自己备查
|
最后贴上SQL算法(简化)
declare @n int,@yscs int
----第一次生成入库运算临时表 先不取转换入库的数据,因为出库成本没赋,肯定拿不到成本
if Exists(select top 1 * from sysobjects where Id=OBJECT_ID(N’rkysjq’) and xtype=‘U’)
begin
drop table rkysjq
end
select wlgg,sum(sl) as sl,(sum(je)/sum(sl)) * 1.000000 as dj into rkysjq from
(
select wlgg, sl * 1.00 as sl, je * 1.00 as je from qc where je >0
union all
select wlgg, sl * 1.00, je * 1.00 from cgrk where je >0
union all
select wlgg, sl * 1.00, je * 1.00 from py where je >0
) a group by wlgg
set @n = 1
set @yscs =8
while @n<=@yscs
begin
-----出库业务表从入库运算临时表取出单价,生成出库临时表
if Exists(select top 1 * from sysobjects where Id=OBJECT_ID(N’ckys’) and xtype=‘U’)
begin
drop table ckys
end
select t1.wlgg, t1.sl, t2.dj ,t1.zhid into ckys from
ck