查询以根据以前的交易显示库存

2021-09-10 00:00:00 sql tsql sql-server

我需要你的帮助..

对于目标将 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.

相关文章