問題描述

我需要你的幫助..

對于目標將 SO(銷售訂單)數量與基于 FIFO(先進先出)的 PO(采購訂單)數量匹配，其中購買的第一個庫存商品必須是最先售出的商品.

for an Objective match SO (Sales Order) quantity to PO (Purchase Order) quantity based on FIFO (First In, First Out) where the first stock items purchased must be the first items sold.

我有一個表 Stock 用于跟蹤庫存進出虛擬庫存倉庫的運動.倉庫最初是空的，然后由于購買庫存(IN")而將庫存移入倉庫，并在出售(OUT")時將庫存移出倉庫.每種類型的庫存項目都由一個 ItemID 標識.由于購買或出售給定項目，每次進出倉庫的庫存都會在 Stock 表中添加一行，由 StockID 標識列中的值唯一標識，并描述有多少項目添加或刪除以及交易日期.

I have a table Stock which use to track the movement of stock in and out of imaginary stock warehouse. The warehouse is initially empty, and stock then moves into the warehouse as a result of a stock purchase (‘IN’) and stock moves out of the warehouse when it is sold (‘OUT’). Each type of stock item is identified by an ItemID. Each movement of stock in or out of the warehouse, due to a purchase or sale of a given item, results in a row being added to the Stock table, uniquely identified by the value in the StockID identify column, and describing how many items were added or removed and the date of the transaction.

餐桌庫存:

 StockId    DocumentID  ItemID  TranDate    TranCode    Quantity
    ------------------------------------------------------------
    1       PO001       A021    2016.01.01  IN          3
    4       SO010       A021    2016.01.02  OUT         2
    2       PO002       A021    2016.01.10  IN          7
    3       PO003       A021    2016.02.01  IN          9
    5       SO011       A021    2016.02.11  OUT         8
    6       SO012       A023    2016.02.12  OUT         6

如何編寫查詢以提供如下表所示的輸出?

How could I write a query to give output like the table below?

SOID    POID    Quantity
------------------------
SO010   PO001   2
SO011   PO001   1
SO011   PO002   7
SO012   PO003   6

推薦答案

因此，鑒于沒有其他人嘗試過，我想我會發布一些類似于答案的內容(我相信).

So, seeing as no one else has given this a go, I figure I'll post something that resembles an answer (I believe).

本質上，你想要做的是根據日期跟蹤你有庫存的東西的數量和已經出去的東西的數量(我沒有考慮進出的多個東西同一天，雖然).

Essentially, what you want to do is keep track of the number of things you have in stock and the number of things that have gone out, based on the date (I haven't accounted for multiple things coming in or going out on the same date, though).

DECLARE @Table TABLE 
(
    DocumentID VARCHAR(10) NOT NULL,
    TranCode VARCHAR(3) NOT NULL,
    TranDate DATE NOT NULL,
    Quantity INT NOT NULL
); -- I'm ignoring the other columns here because they don't seem important to your overall needs.

INSERT @Table (DocumentID, TranCode, TranDate, Quantity)
VALUES 
    ('PO001', 'IN', '2016-01-01', 3),
    ('SO010', 'OUT', '2016-01-02', 2),
    ('PO002', 'IN', '2016-01-10', 7),
    ('PO003', 'IN', '2016-02-01', 9),
    ('SO011', 'OUT', '2016-02-11', 8),
    ('SO012', 'OUT', '2016-02-12', 6);

WITH CTE AS
(
    SELECT DocumentID,
            TranCode,
            TranDate,
            Quantity,
            RunningQuantity = -- Determine the current IN/OUT totals.
                (
                    SELECT SUM(Quantity)
                    FROM @Table 
                    WHERE TranCode = T.TranCode
                    AND TranDate <= T.TranDate
                ),
            PrevQuantity = -- Keep track of the previous IN/OUT totals.
                (
                    SELECT ISNULL(SUM(Quantity), 0)
                    FROM @Table
                    WHERE TranCode = T.TranCode
                    AND TranDate < T.TranDate
                )
    FROM @Table T
)
SELECT Outgoing.DocumentID,
        Incoming.DocumentID,
        Quantity =
            CASE WHEN Outgoing.RunningQuantity <= Incoming.RunningQuantity AND Outgoing.PrevQuantity >= Incoming.PrevQuantity
                 THEN Outgoing.RunningQuantity - Outgoing.PrevQuantity
                 WHEN Outgoing.RunningQuantity <= Incoming.RunningQuantity AND Outgoing.PrevQuantity < Incoming.PrevQuantity
                 THEN Outgoing.RunningQuantity - Incoming.PrevQuantity
                 ELSE Incoming.RunningQuantity - Outgoing.PrevQuantity
            END
FROM CTE Outgoing
JOIN CTE Incoming ON
        Incoming.TranCode = 'IN'
    AND Incoming.RunningQuantity > Outgoing.PrevQuantity
    AND Incoming.PrevQuantity < Outgoing.RunningQuantity
WHERE Outgoing.TranCode = 'OUT'
ORDER BY Outgoing.TranDate;

注意:我強烈建議您以更好的方式跟蹤信息.例如，創建一個表來詳細說明哪些訂單從哪些其他訂單中獲取了什么(訂單交易表或其他東西)，因為雖然通過數據結構方式實現您想要的并非不可能，但如果您只存儲更多有用的數據.

Note: I would highly recommend you keep track of the information in a better way. For example, create a table that actually details which orders took what from which other orders (an order transaction table or something), because while it's not impossible to achieve what you want with the way your data is structured, it's much less complicated if you just store more helpful data.

這篇關于查詢以根據以前的交易顯示庫存的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！