SQL计算销货成本(COGS)问题：FIFO匹配结果数量不符

需求：基于先进先出(FIFO)逻辑计算销货成本(COGS)，现有两个数据表：

• PurchaseActions：包含字段 PurchaseId、PurchaseDate、PurchaseLineId、ItemId、Quantity、Price
• SaleActions：包含字段 SaleId、SaleDate、SaleLineId、ItemId、Quantity、Price

示例数据 #

8月1日：以1美元单价采购60件X商品
8月2日：以2美元单价采购5件X商品
8月5日：以1.5美元单价售出63件X商品

问题 #

预期结果应匹配60件（来自8月1日采购）和3件（来自8月2日采购），但编写的SQL查询返回的是60件和5件。

现有SQL代码 #

WITH RankedPurchases AS (SELECT PurchaseId,
                                PurchaseDate,
                                LineId                                                                                AS PurchaseLineId,
                                ItemId,
                                Quantity,
                                Price,
                                SUM(Quantity) OVER (PARTITION BY ItemId ORDER BY PurchaseDate, PurchaseId)            AS CumulativePurchaseQuantity,
                                SUM(Quantity) OVER (PARTITION BY ItemId ORDER BY PurchaseDate, PurchaseId) - Quantity AS PreviousCumulativePurchaseQuantity
                         FROM @PurchaseActions),
     RankedSales AS (SELECT SaleId,
                            SaleDate,
                            LineId                                                                        AS SaleLineId,
                            ItemId,
                            Quantity,
                            SUM(Quantity) OVER (PARTITION BY ItemId ORDER BY SaleDate, SaleId)            AS CumulativeSaleQuantity,
                            SUM(Quantity) OVER (PARTITION BY ItemId ORDER BY SaleDate, SaleId) - Quantity AS PreviousCumulativeSaleQuantity
                     FROM @SaleActions),
     MatchedSales AS (SELECT s.SaleId,
                             s.SaleLineId,
                             p.PurchaseId,
                             p.PurchaseLineId,
                             s.ItemId,
                             s.Quantity                                                                                    AS SaleQuantity,
                             p.Quantity                                                                                    AS PurchaseQuantity,
                             p.Price,
                             s.PreviousCumulativeSaleQuantity,
                             p.PreviousCumulativePurchaseQuantity,
                             ROW_NUMBER() OVER (PARTITION BY s.SaleId, s.SaleLineId ORDER BY p.PurchaseDate, p.PurchaseId) AS RowNum
                      FROM RankedSales s
                               JOIN RankedPurchases p ON s.ItemId = p.ItemId
                      WHERE p.CumulativePurchaseQuantity > s.PreviousCumulativeSaleQuantity
                        AND p.PreviousCumulativePurchaseQuantity < s.CumulativeSaleQuantity)
SELECT SaleId,
       SaleLineId,
       PurchaseId,
       PurchaseLineId,
       CASE
           WHEN RowNum = 1 THEN
               CASE
                   WHEN SaleQuantity <= PurchaseQuantity THEN SaleQuantity
                   ELSE PurchaseQuantity
                   END
           ELSE
               CASE
                   WHEN SaleQuantity - (PreviousCumulativeSaleQuantity - PreviousCumulativePurchaseQuantity) <= PurchaseQuantity
                       THEN SaleQuantity - (PreviousCumulativeSaleQuantity - PreviousCumulativePurchaseQuantity)
                   ELSE PurchaseQuantity
                   END
           END AS Quantity,
       Price
FROM MatchedSales
ORDER BY ItemId, SaleId, SaleLineId, RowNum

内容的提问来源于stack exchange，提问作者Kanan Mammadov