如何在SQL Server中使用FIFO计算销售成本

Question

我想用先进先出的方法计算商品的销售成本。

我知道我卖了多少啤酒。根据我买这些啤酒的价格，销售这些啤酒的成本是多少？因此，根据FIFO估值方法，我销售的7个Peroni的价值是1.70 on。

如何在SQL Server中进行计算。

我将在同一时间为许多产品和许多分支解决这个问题，所以我想使用一种不涉及游标(或任何其他类型的循环)的方法。

​

​

-- 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
*/

Answer 1

这可能不是很有效，但它向您展示了一种可以实现这一点的方法，这应该有助于您提出最终的解决方案。我想这个过程需要更多的复杂性来解释诸如库存浪费之类的事情，但我将把它留给您：

查询

-- 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 |
+-------------+----------+------------------+

Answer 2

下面的查询可能会对您有所帮助：

declare @maxQty int
select @maxQty = max(SalesQty) from #StockResults
;WITH AllNumbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number+1 FROM AllNumbers WHERE Number < @maxQty
)

select ProductCode, SalesQty, SUM(Cost) as CostOfSalesValue  from
(
    SELECT SR.ProductCode, SR.SalesQty, DLM.RN, DLM.Cost FROM #StockResults SR
    outer apply
    (
        select ROW_NUMBER()OVER (order by DeliveryDate asc) as RN, Dl.Cost from #Deliveries Dl
        inner join AllNumbers AL on AL.Number <= Dl.Quantity
        where Dl.ProductCode = SR.ProductCode
    ) as DLM

) result
where RN <= SalesQty 
group by ProductCode, SalesQty