久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

查詢以根據以前的交易顯示庫存

Query to show stock based on previous transactions(查詢以根據以前的交易顯示庫存)
本文介紹了查詢以根據以前的交易顯示庫存的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學習吧!

問題描述

我需要你的幫助..

對于目標將 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模板網!

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!

相關文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個子標記轉換為具有多個分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個表創建視圖?)
Create calculated value based on calculated value inside previous row(根據前一行內的計算值創建計算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對?) - IT屋-程序員軟件開發技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉換為日期/月份編號(問題和答案的組合))
主站蜘蛛池模板: 自拍 亚洲 欧美 老师 丝袜 | 精品亚洲一区二区三区 | 欧美日韩在线一区二区三区 | 日本不卡一区 | 久久高清国产 | 久色视频在线观看 | 日本高清不卡视频 | 999免费视频 | 国产在线1区 | 男女在线免费观看 | 青青久草| 高清亚洲| 天天看天天操 | 精精国产xxxx视频在线播放 | 亚洲免费网 | 久久性色 | 亚洲女人天堂网 | 一区二区三区韩国 | 一区二区三区在线 | 欧美综合久久 | 国产精品久久久久久久久免费桃花 | 午夜三级在线观看 | 一级片免费网站 | www.888www看片| 天天操天天插天天干 | 日韩不卡一区二区三区 | 欧美一区二区在线免费观看 | 婷婷色综合 | 久久久久网站 | 中文字幕在线三区 | 国产精品久久久久久久久免费软件 | 美人の美乳で授乳プレイ | 成人免费看片网 | 一区二区在线不卡 | 日韩淫片免费看 | 亚洲国产精品99久久久久久久久 | 天天操天天操 | 午夜欧美| 91av免费看 | 免费在线观看成人 | 一区二区中文字幕 |