sqlsql-serverfifo

How to value cost of sales using FIFO in SQL Server

I want value the cost of goods sold using the FIFO method.

I know how many beers I sold. Based on my price I bought those beers at, what is the cost of those sales? So, my sales of 7 Peronis are valued at £1.70 -- based on the FIFO valuation method.

How do I calculate in SQL Server.

I am going to be working this out for many products and from many branches at the same time, so I would like to use a method that does not involve cursors (or any other types of loops).

Excel Calculations

-- SETUP
DROP TABLE IF EXISTS #Deliveries;
CREATE TABLE #Deliveries (DeliveryDate DATE, ProductCode VARCHAR(10), Quantity INT, Cost DECIMAL(6,2));

INSERT INTO #Deliveries (DeliveryDate, ProductCode, Quantity, Cost)
VALUES 
('2020-11-23', 'PERONI', 2, 0.20), ('2020-11-24', 'PERONI', 4, 0.30), ('2020-11-25', 'PERONI', 7, 0.10), 
('2020-11-23', 'BUDWEISER', 5, 0.20), ('2020-11-24', 'BUDWEISER', 5, 0.50), ('2020-11-25', 'BUDWEISER', 4, 0.80);

DROP TABLE IF EXISTS #StockResults;
CREATE TABLE #StockResults (ProductCode VARCHAR(10), SalesQty INT, CostOfSalesValue DECIMAL(6,2));

INSERT INTO #StockResults (ProductCode, SalesQty)
VALUES ('PERONI', 7), ('BUDWEISER', 4);

SELECT * FROM #Deliveries;
SELECT * FROM #StockResults;


-- DESIRED RESULT

/*
ProductCode     SalesQty    CostOfSalesValue
PERONI          7           1.70
BUDWEISER       4           0.80
*/
Solution

  • This is probably not very efficient but it shows you one way in which this can be achieved which should help you come up with your finished solution. I would imagine that there needs to be a lot more complexity built into this process to account for things like stock wastage, but I'll leave that up to you:

    Query

    -- SETUP
declare @Deliveries table (DeliveryDate date, ProductCode varchar(10), Quantity int, Cost decimal(6,2));
insert into @Deliveries (DeliveryDate, ProductCode, Quantity, Cost) values ('2020-11-23', 'PERONI', 2, 0.20), ('2020-11-24', 'PERONI', 4, 0.30), ('2020-11-25', 'PERONI', 7, 0.10),('2020-11-23', 'BUDWEISER', 5, 0.20), ('2020-11-24', 'BUDWEISER', 5, 0.50), ('2020-11-25', 'BUDWEISER', 4, 0.80);

declare @StockResults table (ProductCode varchar(10), SalesQty int);
insert into @StockResults (ProductCode, SalesQty) values ('PERONI', 7), ('BUDWEISER', 4);

-- QUERY
with r as
(
    select d.ProductCode
          ,d.DeliveryDate
          ,d.Quantity
          ,d.Cost
          ,isnull(sum(d.Quantity) over (partition by d.ProductCode order by d.DeliveryDate rows between unbounded preceding and 1 preceding),0) as RunningQuantityStart
          ,sum(d.Quantity) over (partition by d.ProductCode order by d.DeliveryDate) as RunningQuantityEnd
    from @Deliveries as d
)
select r.ProductCode
      ,s.SalesQty
      ,sum(case when r.RunningQuantityEnd >= s.SalesQty
                then (s.SalesQty - r.RunningQuantityStart) * r.Cost
                else (r.RunningQuantityEnd - r.RunningQuantityStart) * r.Cost
                end
          ) as CostOfSalesValue
from r
    join @StockResults as s
        on r.ProductCode = s.ProductCode
            and r.RunningQuantityStart < s.SalesQty
group by r.ProductCode
        ,s.SalesQty;

    Output

    +-------------+----------+------------------+
| ProductCode | SalesQty | CostOfSalesValue |
+-------------+----------+------------------+
| BUDWEISER   |        4 |             0.80 |
| PERONI      |        7 |             1.70 |
+-------------+----------+------------------+