使用SQL基于FIFO逻辑计算物料库存在站点的存放天数

Hey there! Let's work through this FIFO stock age calculation problem together. Your goal is to left join the Stock and Receipts tables, identify the earliest remaining batch (per FIFO rules) that contributes to current inventory, and calculate how long that stock has been stored. Here's how to tackle it:

Understanding the Core FIFO Logic #

First, let's recap with the Blade example to make sure we're aligned:

• Total units received for Blade: 20 + 10 + 5 = 35
• Current stock: 8 units
• Using FIFO, we exhaust the oldest batches first: all 20 units from 1/3/2020 are used, 7 units from the 12/10/2021 batch are used (leaving 3), and all 5 units from the 1/5/2022 batch remain.
• We need to pick the earliest batch that still has leftover stock (12/10/2021) to calculate the stock age—this represents the oldest portion of the current inventory.

SQL Solution (Works with Modern Databases) #

This query uses window functions (supported in PostgreSQL, SQL Server, MySQL 8.0+, etc.) to calculate cumulative receipts and identify the target batch:

WITH ReceiptsWithCumulative AS (
    SELECT
        Item,
        ReceiptDate,
        Quantity,
        -- Calculate cumulative received units from oldest to newest
        SUM(Quantity) OVER (
            PARTITION BY Item
            ORDER BY ReceiptDate ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CumulativeReceived,
        -- Total units received for the item
        SUM(Quantity) OVER (PARTITION BY Item) AS TotalReceived
    FROM Receipts
),
TargetBatches AS (
    SELECT
        Item,
        ReceiptDate,
        -- Mark the first batch that has remaining stock
        ROW_NUMBER() OVER (
            PARTITION BY Item
            ORDER BY ReceiptDate ASC
        ) AS BatchRank
    FROM ReceiptsWithCumulative r
    JOIN Stock s ON r.Item = s.Item
    -- Find batches where cumulative received exceeds the number of units already used
    WHERE r.CumulativeReceived > (r.TotalReceived - s.CurrentStock)
)
SELECT
    s.Item,
    s.CurrentStock,
    s.Value,
    t.ReceiptDate,
    -- Calculate days between receipt date and your specified current date (2/1/2022)
    DATEDIFF(DAY, t.ReceiptDate, '2022-02-01') AS AgeInDays
FROM Stock s
LEFT JOIN TargetBatches t ON s.Item = t.Item AND t.BatchRank = 1
ORDER BY s.Item;

Breaking Down the Query #

Let's walk through each part:

1. CTE 1: ReceiptsWithCumulative

   • We group receipts by Item, order them from oldest to newest, and calculate two values:
     • CumulativeReceived: Running total of units received up to each batch
     • TotalReceived: Total units ever received for the item

2. CTE 2: TargetBatches

   • We join this cumulative data with the Stock table to find batches that still contribute to current inventory. The key filter is r.CumulativeReceived > (r.TotalReceived - s.CurrentStock)—this finds the first batch where the cumulative received units exceed the number of units already used (total received minus current stock).
   • We use ROW_NUMBER() to mark the first (oldest) batch that meets this condition, since that's the oldest remaining batch in our current stock.

3. Final SELECT

   • We left join the Stock table with our target batches to include all items (even those with no receipts, like Screen). For items with no receipts, ReceiptDate and AgeInDays will be NULL, which matches your expected result.

Verifying with Your Examples #

• Blade: TotalReceived - CurrentStock = 35 - 8 = 27. The first batch where cumulative received exceeds 27 is 12/10/2021 (cumulative 30), so that's our target date.
• Table: TotalReceived - CurrentStock = 27 - 15 = 12. The first batch where cumulative received exceeds 12 is 5/1/2021 (cumulative 17), which matches your expected result.

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