表A(tblA)
noid fType fQty fPrice
1 1 20 10
2 1 30 11
3 2 15 ?
4 2 10 ?
5 1 100 9
6 2 120 ?现在是要计算fPrice=?的值,原理:
ftype=2 相当于出库 fType=1 相当于入库 先入库的先出库
所以noid=3的fPrice= noid=1的fPrice ,noid=3 的出库后 noid=1的还有5个
noid=4 的出库 fPrice=10.5
noid=6的出库 fPrice=9.41结果如下:noid fType fQty fPrice
1 1 20 10
2 1 30 11
3 2 15 10
4 2 10 (5*10+5*11)/10 =10.5
5 1 100 9
6 2 120 (25*11+95*9)/120 =9.41
noid fType fQty fPrice
1 1 20 10
2 1 30 11
3 2 15 ?
4 2 10 ?
5 1 100 9
6 2 120 ?现在是要计算fPrice=?的值,原理:
ftype=2 相当于出库 fType=1 相当于入库 先入库的先出库
所以noid=3的fPrice= noid=1的fPrice ,noid=3 的出库后 noid=1的还有5个
noid=4 的出库 fPrice=10.5
noid=6的出库 fPrice=9.41结果如下:noid fType fQty fPrice
1 1 20 10
2 1 30 11
3 2 15 10
4 2 10 (5*10+5*11)/10 =10.5
5 1 100 9
6 2 120 (25*11+95*9)/120 =9.41
------------------------------------------------------------------------------------
--生成测试数据
Create Table tmp_IntPut(
FItemID Int ,
FQty Decimal(18,4),
FCost Decimal(18,4),
FID Int,
FRow Int,
FR Int Identity(1,1) )Create Table tmp(Fid Int,
FItemID Int,
FQty DECIMAL(18,4),
FCost Decimal(18,4))
Insert Into tmp_IntPut(FItemID,FQty,FCost,FID)
Select 1, 1,20.00,1 Union
Select 1, 5,22.00,2 Union
Select 1,10,21.00,3 Union
Select 1,10,19.00,3 UnionInsert Into tmp(Fid,FItemID,FQty,FCost)
Select 1,1, 1,0 Union
Select 2,1, 6,0 Union
Select 3,1, 2,0 Union
Select 4,1,10,0
GO
--创建存储过程
CREATE PROCEDURE SP_PROCESS
AS
BEGIN
IF NOT EXISTS(SELECT 1 FROM tmp) OR NOT EXISTS(SELECT 1 FROM tmp_IntPut)
RETURN
--借助游标实现数据处理过程
DECLARE @FR int,@FCost Decimal(18,4)
DECLARE @FR_I int,@FQty_I DECIMAL(18,4),@FCost_I Decimal(18,4)
DECLARE @FID int,@FQty DECIMAL(18,4)
SELECT @FR=MAX(FR) FROM tmp_IntPut
DECLARE c1 CURSOR FOR
SELECT Fid,FQty FROM tmp ORDER BY Fid
DECLARE c2 CURSOR FOR
SELECT FR ,FQty,FCost FROM tmp_IntPut ORDER BY FR
OPEN c1
FETCH NEXT FROM c1 INTO @FID,@FQty
OPEN c2
FETCH NEXT FROM c2 INTO @FR_I,@FQty_I,@FCost_I
WHILE @@FETCH_STATUS=0
BEGIN
SET @FCost=0
IF @FR_I=@FR
SET @FCost=@FQty*@FCost_I
ELSE
BEGIN
WHILE @FQty>0
BEGIN
IF @FQty_I>0
BEGIN
IF(@FQty>@FQty_I)
BEGIN
SET @FCost=@FCost+@FQty_I*@FCost_I
SET @FQty=@FQty-@FQty_I
END
ELSE
BEGIN
SET @FCost=@FCost+@FQty*@FCost_I
SET @FQty_I=@FQty_I-@FQty
SET @FQty=0
END
END
BEGIN
IF @FR_I=@FR
BEGIN
SET @FCost=@FCost+@FQty*@FCost_I
SET @FQty=0
END
ELSE
FETCH NEXT FROM c2 INTO @FR_I,@FQty_I,@FCost_I
END
END
END
UPDATE tmp SET FCost=@FCost WHERE Fid=@FID
FETCH NEXT FROM c1 INTO @FID,@FQty
END
CLOSE c2
DEALLOCATE c2
CLOSE c1
DEALLOCATE c1
END
GO--执行存储过程
EXEC SP_PROCESS
GO--查看执行结果
SELECT * FROM Tmp
/*
Fid FItemID FQty FCost
----------- ----------- -------------------- --------------------
1 1 1.0000 20.0000
2 1 6.0000 131.0000
3 1 2.0000 42.0000
4 1 10.0000 210.0000
*/
--删除测试数据
DROP TABLE tmp,tmp_IntPut
--------------------------------------------------------------------------------------生成测试数据
Create Table tmp_IntPut(
FItemID Int ,
FQty Decimal(18,4),
FCost Decimal(18,4),
FID Int,
FRow Int,
FR Int Identity(1,1) )Create Table tmp(
Fid Int,
FItemID Int,
FQty DECIMAL(18,4),
FCost Decimal(18,4))
Insert Into tmp_IntPut(FItemID,FQty,FCost,FID)
Select 1, 1,20.00,1 as FID Union
Select 1, 5,22.00,2 Union
Select 1,10,21.00,3Insert Into tmp(Fid,FItemID,FQty,FCost)
Select 1,1, 1,0 Union
Select 2,1, 6,0 Union
Select 3,1, 2,0 Union
Select 4,1,10,0 Union
Select 5,1,-6,0
GO
--创建存储过程
CREATE PROCEDURE SP_PROCESS
AS
BEGIN
IF NOT EXISTS(SELECT 1 FROM tmp) OR NOT EXISTS(SELECT 1 FROM tmp_IntPut)
RETURN
--借助游标实现数据处理过程
DECLARE @FR int,@FCost Decimal(18,4),@PCOST Decimal(18,4)
DECLARE @FR_I int,@FQty_I DECIMAL(18,4),@FCost_I Decimal(18,4)
DECLARE @FID int,@FQty DECIMAL(18,4)
SELECT @FR=MAX(FR) FROM tmp_IntPut
SET @PCOST=0
DECLARE c1 CURSOR FOR
SELECT Fid,FQty FROM tmp ORDER BY Fid
DECLARE c2 CURSOR FOR
SELECT FR ,FQty,FCost FROM tmp_IntPut ORDER BY FR
OPEN c1
FETCH NEXT FROM c1 INTO @FID,@FQty
OPEN c2
FETCH NEXT FROM c2 INTO @FR_I,@FQty_I,@FCost_I
WHILE @@FETCH_STATUS=0
BEGIN
SET @FCost=0
IF @FR_I=@FR
SET @FCost=@FQty*@FCost_I
ELSE
BEGIN
IF @FQty<0
BEGIN
SET @FCost_I=(@FCost_I*@FQty_I-@PCost*@FQty)/(@FQty_I-@FQty)
SET @FQty_I=@FQty_I-@FQty
SET @FCost=@PCost*@FQty
SET @FQty=0
END
WHILE @FQty>0
BEGIN
IF @FQty_I>0
BEGIN
IF(@FQty>@FQty_I)
BEGIN
SET @FCost=@FCost+@FQty_I*@FCost_I
SET @FQty=@FQty-@FQty_I
END
ELSE
BEGIN
SET @FCost=@FCost+@FQty*@FCost_I
SET @FQty_I=@FQty_I-@FQty
SET @FQty=0
END
END
BEGIN
IF @FR_I=@FR
BEGIN
SET @FCost=@FCost+@FQty*@FCost_I
SET @FQty=0
END
ELSE
FETCH NEXT FROM c2 INTO @FR_I,@FQty_I,@FCost_I
END
END
END
UPDATE tmp SET FCost=@FCost,@PCost=@FCost/FQty WHERE Fid=@FID
FETCH NEXT FROM c1 INTO @FID,@FQty
END
CLOSE c2
DEALLOCATE c2
CLOSE c1
DEALLOCATE c1
END
GO--执行存储过程
EXEC SP_PROCESS
GO--查看执行结果
SELECT * FROM Tmp
GO
/*
Fid FItemID FQty FCost
----------- ----------- -------------------- --------------------
1 1 1.0000 20.0000
2 1 6.0000 131.0000
3 1 2.0000 42.0000
4 1 10.0000 210.0000
5 1 -6.0000 -126.0000
*/
--删除测试数据
DROP PROCEDURE SP_PROCESS
DROP TABLE tmp,tmp_IntPut
if object_id('tempdb..#tmp') is not null
drop table #tmp
if object_id('tempdb..#tmp2') is not null
drop table #tmp2
if object_id('tbTest') is not null
drop table tbTest
GO
create table tbTest(noid int,fType int,fQty int,fPrice decimal(10,2))
insert tbTest
select 1, 1, 20, 10 union all
select 2, 1, 30, 11 union all
select 3, 2, 15, NULL union all
select 4, 2, 10, NULL union all
select 5, 1, 100, 9 union all
select 6, 2, 120, NULL
GO----生成临时表1,用于实现每次出库对应的入库关系(即每次出库都来自哪几个入库)
SELECT a.noid,a.fQty,b.noid as b_noid,b.fQty as b_fQty,b.fPrice as b_fPrice,
stock =
isnull((select sum(fqty) from tbTest where fType = 1 and noid <= b.noid),b.fQty)-
isnull((select sum(fqty) from tbTest where fType = 2 and noid <= a.noid),a.fQty)
INTO #tmp
FROM tbTest as a
INNER JOIN tbTest as b
ON
(a.fType = 2 and b.fType = 1 and
isnull((select sum(fqty) from tbTest where fType = 2 and noid <= a.noid),0) >=
isnull((select sum(fqty) from tbTest where fType = 1 and noid < b.noid),0))
AND
(b.noid >=isnull((select max(y.noid) from tbTest as x inner join tbTest as y
on x.noid = (select max(noid) from tbTest where noid < a.noid and fType = 2)
and x.fType = 2 and y.fType = 1 and
isnull((select sum(fqty) from tbTest where fType = 2 and noid <= x.noid),0) >=
isnull((select sum(fqty) from tbTest where fType = 1 and noid < y.noid),0)
),0))
ORDER BY a.noid,b.noid----生成临时表2,用于获得每次出库时之前的库存
SELECT noid,stock INTO #tmp2 FROM #tmp AS t
WHERE not exists(select 1 from #tmp where noid = t.noid and b_noid > t.b_noid)----更新
UPDATE a SET fPrice = b.amounts
FROM tbTest AS a
LEFT JOIN (
SELECT noid,fQty,sum(amounts)/fQty AS amounts FROM
(select a.noid,a.fQty,
amounts = a.b_fPrice*
case
when a.b_noid = (select min(b_noid) from #tmp where noid = a.noid) /*本次出库的第一次出库*/
then isnull(t.stock,a.fQty) /*本次出库的第一次出库数量来自于上一次出库结束的库存*/
else
case
when /*当截至某次入库的累计入库仍不够本次出库时,则此次入库全部出库*/
a.fQty - isnull(t.stock,0) - isnull((select sum(b_fQty) from #tmp where noid = a.noid and b_noid <= a.b_noid),0) >= 0
then a.b_fQty
else /*否则表示截至某次入库时,则累计的入库已满足出库需要,计算从该次入库出库了多少*/
/*最内层的减法"(...)-t.stock"用于去除累计入库时多计算的误差*/
a.b_fQty + (a.fQty -(isnull((select sum(b_fQty) from #tmp where noid = a.noid and b_noid <= a.b_noid),0)-
((select b_fQty from #tmp where noid = a.noid and
b_noid = (select min(b_noid) from #tmp where noid = a.noid)) - t.stock)))
end
end
from #tmp as a
left join #tmp2 as t on a.noid = (select min(noid) from #tmp2 where noid > t.noid)) AS w
GROUP BY noid,fQty)
AS b ON a.noid = b.noid
WHERE a.fType = 2----查看
SELECT * FROM tbTest----清除测试环境
DROP TABLE tbTest,#tmp,#tmp2/*结果
noid fType fQty fPrice
----------- ----------- ----------- ------------
1 1 20 10.00
2 1 30 11.00
3 2 15 10.00
4 2 10 10.50
5 1 100 9.00
6 2 120 9.42
*/