Calculating Cost of Sales in SQL using FIFO method

Question

From a stock transaction table I've created following sorted view table to be ready for FIFO:

rowN	date_	stockCode	sign_	amount	unitPrice
1	'2022-01-20'	ABC	in	5	29.20
2	'2022-01-22'	ABC	in	3	32.50
3	'2022-01-23'	ABC	out	7	40.00
4	'2022-01-23'	ABC	out	1	42.00
5	'2022-01-01'	XYZ	in	3	20.50
6	'2022-01-03'	XYZ	out	3	25.00

and I want to create a select query which looks like the previous table with only "out" rows and added cost_of_sales columns which is FIFO cost of that sale. But my knowledge of SQL is limited by just joins and sum over partitions.

The resulting table in my mind should look like this:

rowN	date_	stockCode	sign_	amount	unitPrice	cost_of_sales_uP	cost_of_sales
3	'2022-01-23'	ABC	out	7	40.00	30.1428	211.00
4	'2022-01-23'	ABC	out	1	42.00	32.50	32.50
6	'2022-01-03'	XYZ	out	3	25.00	20.50	61.50

I have no idea how to achieve this. Any help and guidance is appreciated. Result table doesn't have to be exactly like that but the main idea is there.

Thanks!

Answer 1

The following method might not be the fastest.
But it gets the job done.

First the incoming are unfolded into a temp table.

Then, by looping over the outgoing, each incoming unit is assigned to an outgoing on a First-In-First-Out basis.

The final query then uses the results of the temp table to calculate the total & average of the incoming.

IF OBJECT_ID('tempdb..#tmpStock') IS NOT NULL DROP TABLE #tmpStock;
CREATE TABLE #tmpStock (
  id int identity primary key,
  code varchar(30),
  date_in date,
  rowN int,
  unitPrice decimal(10,2),
  rowN_out int
);

--
-- Using a recursive CTE to unfold the incoming for the temp table
--
with RCTE as (
 select stockCode, date_, rowN, amount, unitPrice
 , 1 as lvl
 from stock_transactions
 where sign_ = 'in'
 
 union all
 
 select stockCode, date_, rowN, amount, unitPrice
 , lvl + 1
 from RCTE
 where lvl < amount
)
insert into #tmpStock (code, date_in, rowN, unitPrice)
select stockCode, date_, rowN, unitPrice
from RCTE
order by stockCode, date_, rowN
option (maxrecursion 0);

DECLARE @IdOut INT = 1;
DECLARE @RowsOut INT = 0;
DECLARE @code VARCHAR(30);
DECLARE @amount SMALLINT;
DECLARE @date DATE;
DECLARE @rowN INT;

DECLARE @StockOut TABLE (
  id int identity primary key, 
  code varchar(30),
  date_out date, 
  rowN int,
  amount smallint
);

insert into @StockOut (code, date_out, rowN, amount)
select stockCode, date_, rowN, amount
from stock_transactions
where sign_ = 'out'
order by stockCode, date_, rowN;

SELECT @RowsOut = COUNT(*) FROM @StockOut;
 
WHILE @IdOut <= @RowsOut
BEGIN
   SELECT 
     @code = code
   , @amount = amount
   , @date = date_out
   , @rowN = rowN
   FROM @StockOut
   WHERE id = @IdOut;

   ;WITH cte_in as (
     select *
     , rn = row_number() over (order by date_in, rowN)
     from #tmpStock 
     where code = @code 
       and date_in <= @date
       and rowN_out is null
   ) 
   UPDATE cte_in 
   SET rowN_out = @rowN
   WHERE rn <= @amount;

   SET @IdOut = @IdOut + 1;
END;

select * from #tmpStock

id | code | date_in    | rowN | unitPrice | rowN_out
-: | :--- | :--------- | ---: | --------: | -------:
 1 | ABC  | 2022-01-20 |    1 |     29.20 |        3
 2 | ABC  | 2022-01-20 |    1 |     29.20 |        3
 3 | ABC  | 2022-01-20 |    1 |     29.20 |        3
 4 | ABC  | 2022-01-20 |    1 |     29.20 |        3
 5 | ABC  | 2022-01-20 |    1 |     29.20 |        3
 6 | ABC  | 2022-01-22 |    2 |     32.50 |        3
 7 | ABC  | 2022-01-22 |    2 |     32.50 |        3
 8 | ABC  | 2022-01-22 |    2 |     32.50 |        4
 9 | XYZ  | 2022-01-01 |    5 |     20.50 |        6
10 | XYZ  | 2022-01-01 |    5 |     20.50 |        6
11 | XYZ  | 2022-01-01 |    5 |     20.50 |        6

SELECT o.*
, CAST(i.AveragePriceIn AS DECIMAL(10,2)) AS cost_of_sales_uP
, i.TotalPriceIn AS cost_of_sales
FROM stock_transactions o
LEFT JOIN (
  SELECT rowN_out
  , AVG(unitPrice) as AveragePriceIn
  , SUM(unitPrice) as TotalPriceIn
  FROM #tmpStock
  GROUP BY rowN_out
) i on i.rowN_out = o.rowN
WHERE o.sign_ = 'out'
ORDER BY o.rowN;

rowN | date_      | stockCode | sign_ | amount | unitPrice | cost_of_sales_uP | cost_of_sales
---: | :--------- | :-------- | :---- | -----: | --------: | ---------------: | ------------:
   3 | 2022-01-23 | ABC       | out   |      7 |     40.00 |            30.14 |        211.00
   4 | 2022-01-23 | ABC       | out   |      1 |     42.00 |            32.50 |         32.50
   6 | 2022-01-03 | XYZ       | out   |      3 |     25.00 |            20.50 |         61.50

Demo on db<>fiddle here