SQLServer中用先进先出思想求成本价和平均成本单价

时间：2021-06-30 03:05:02

1、首先是创建表：

create table #in

(

id int identity(1,1),

TDate datetime not null,

goodcode varchar(10) ,

InNum decimal(20,4) null, --入库数量

Price money null --入库金额

)

create table #out

(

id int identity(1,1),

TDate datetime not null,

goodcode varchar(10) ,

OutNum decimal(20,4) null, --出库数量

salePrice money null, --销售价

costprice money null, --求成本价

costmoney money null --求成本金额

)

2、插入表数据：

 --入库

 insert into #in(tdate,goodcode,InNum,Price) values('2016-1-1','',10,5)

 insert into #in(tdate,goodcode,InNum,Price) values('2016-1-25','',60,6)

 insert into #in(tdate,goodcode,InNum,Price) values('2016-3-1','',20,5.5)

 insert into #in(tdate,goodcode,InNum,Price) values('2016-1-18','',30,6)

 insert into #in(tdate,goodcode,InNum,Price) values('2016-4-1','',40,8)

 --出库

 insert into #out(tdate,goodcode,outnum,salePrice,costprice) values('2016-1-2','',30,6,0)

 insert into #out(tdate,goodcode,outnum,salePrice,costprice) values('2016-4-1','',40,8,0)

 select * from #in order by tdate

 select * from #out order by tdate

3、

--按 先进先出法 算成本价
/*
1 2016-01-02 00:00:00.000 001 30.0000 6.0000 .0000 = （10*5 + 20 *6 ）/30
成本金额 = 10*5 + 20 *6
2 2016-04-01 00:00:00.000 001 40.0000 8.0000 .0000 = （10*6 + 30 *6 ）/40
成本金额 = 10*6 + 30 *6
*/

SELECT

    o.id,o.tdate,o.goodcode,o.outnum,o.salePrice

    ,costprice=CAST(SUM((CASE WHEN i.SumInNum>o.Sumoutnum THEN o.Sumoutnum ELSE i.SumInNum END -CASE WHEN o.Sumoutnum-o.OutNum>i.SumInNum-i.InNum

     THEN o.Sumoutnum-o.OutNum ELSE i.SumInNum-i.InNum END)*i.Price)/o.outnum AS MONEY)

    ,costmoney=CAST(SUM((CASE WHEN i.SumInNum>o.Sumoutnum THEN o.Sumoutnum ELSE i.SumInNum END -CASE WHEN o.Sumoutnum-o.OutNum>i.SumInNum-i.InNum

    THEN o.Sumoutnum-o.OutNum ELSE i.SumInNum-i.InNum END)*i.Price) AS MONEY)

FROM

(SELECT *,SumInNum=(SELECT SUM(InNum) FROM #in WHERE goodcode=i.goodcode AND id<=i.id) FROM #in AS i) AS i,

(SELECT *,Sumoutnum=(SELECT SUM(outnum) FROM #out WHERE goodcode=i.goodcode AND id<=i.id) FROM #out AS i) AS o

WHERE i.goodcode=o.goodcode AND i.SumInNum-i.InNum<o.Sumoutnum AND o.Sumoutnum-o.OutNum<i.SumInNum

GROUP BY o.id,o.tdate,o.goodcode,o.outnum,o.salePrice
 /*

 id    tdate    goodcode    outnum    salePrice    costprice    costmoney

 1    2016-01-02 00:00:00.000    001    30.0000    6.00    5.6667    170.00

 2    2016-04-01 00:00:00.000    001    40.0000    8.00    6.00    240.00

 */

相关文章