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