SQL Server uses FIFO thinking to find cost price and average cost unit price

1, the first is to create a table:

 
create table #in
(
id int identity (1,1),
TDate datetime not null,
goodcode varchar (10),
InNum decimal (20,4) null, --incoming number
Price money null
)
 

create table #out
(
id int identity (1,1),
TDate datetime not null,
goodcode varchar (10),
OutNum decimal (20,4) null, --Number of outbounds
salePrice money null, --sale price
costprice money null,-
costmoney money null-Find the cost amount
)

2. Insert Table data:

 
1-storage
  2 insert into #in (tdate, goodcode, InNum, Price) values (‘2016-1-1’, ‘001’, 10,5)
  3 insert into #in (tdate, goodcode, InNum, Price) values (‘2016-1-25’, ‘001’, 60,6)
  4 insert into #in (tdate, goodcode, InNum, Price) values (‘2016-3-1’, ‘002’, 20, 5.5)
  5 insert into #in (tdate, goodcode, InNum, Price) values (‘2016-1-18’, ‘001’, 30,6)
  6 insert into #in (tdate, goodcode, InNum, Price) values (‘2016-4-1’, ‘001’, 40,8)
  7
  8-Out of stock
  9 insert into #out (tdate, goodcode, outnum, salePrice, costprice) values (‘2016-1-2’, ’001’, 30,6,0)
10 insert into #out (tdate, goodcode, outnum, salePrice, costprice) values (‘2016-4-1’, ‘001’, 40,8,0)
11
12 select * from #in order by tdate
13 select * from #out order by tdate

3.

--The cost price is calculated by FIFO method
/*
12016-01-02 00:00:00.00000130.00006.0000.0000 = (10*5 + 20 *6)/30
Cost Amount = 10*5 + 20 *6
22016-04-01 00:00:00.00000140.00008.0000.0000 = (10*6 + 30 *6)/40
Cost Amount = 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
1 /*
2 id    tdate    goodcode    outnum    salePrice    costprice    costmoney
3 1    2016-01-02 00:00:00.000    001    30.0000    6.00    5.6667    170.00
4 2    2016-04-01 00:00:00.000    001    40.0000    8.00    6.00    240.00
5 */

SQL Server uses FIFO thinking to find cost price and average cost unit price