FIFO匹配第一股买入首先卖sql

I have data that looks like this:

我的数据看起来像这样：

Stock buys and sells

股票买卖

I need a query to apply the FIFO method to the Buys and Sells so I get a table that looks like this: FIFO Buys and Sells

我需要一个查询来将FIFO方法应用于买入和卖出，因此我得到一个如下所示的表：FIFO买入和卖出

I want to be able to match the first buy/s to the first sells with buys on the left and sells on the right. If there is no sell then Nulls should be applied on the right and if there is no buy then nulls should be applied on the left. The brokerage transaction key can be used as the order in which the trades occurred. This is what I've tried so far. Any help would be much appreciated!

我希望能够将第一次买入与第一次买入匹配，并在左边买入并在右边卖出。如果没有卖出，则应在右侧应用Null，如果没有买入，则应在左侧应用空值。经纪交易密钥可以用作交易发生的顺序。这是我到目前为止所尝试的。任何帮助将非常感激！

SELECT  a.ACCT_ID, a.Trade_Date_Key, a.Brokerage_Transaction_Key, a.Buy_Sell_Code, a.Principal_Amt, a.Security_Quantity
     , (a.Security_Quantity + b.Security_Quantity) CUMULATIVE_POSITION
     , a.SHARE_PRICE
     , (A.Principal_Amt + B.Principal_Amt) CUMULATIVE_VALUE
from #TRANSACTIONS_WITH_RANK a
     left join #TRANSACTIONS_WITH_RANK b 
     on a.acct_id = b.acct_id and a.rank = b.rank + 1
ORDER BY BROKERAGE_TRANSACTION_KEY

1 个解决方案

#1

In your question you mention matching the first buy(s) with the first sell(s), but your example output seems to ignore that part. Here's an example of how to do if you want to match the first buy(s) to first sell(s) based on the Acct_ID and Trade_Date

在您的问题中，您提到将第一批买入与第一批买入匹配，但您的示例输出似乎忽略了该部分。以下是根据Acct_ID和Trade_Date将第一批买入与首次买入相匹配的方法示例

SELECT buy.*, sell.*
FROM #TRANSACTIONS_WITH_RANK buy
    INNER JOIN (
        SELECT MIN(Trade_Date) Trade_Date
        FROM #TRANSACTIONS_WITH_RANK
        WHERE Buy_Sell_Code = 'B'
        GROUP BY Acct_ID
    ) TDateBuy
    ON buy.Trade_Date = TDateBuy.Trade_Date
    FULL OUTER JOIN #TRANSACTIONS_WITH_RANK sell
        INNER JOIN (
            SELECT MIN(Trade_Date) Trade_Date
            FROM #TRANSACTIONS_WITH_RANK
            WHERE Buy_Sell_Code = 'S'
            GROUP BY Acct_ID
        ) TDateSell
        ON sell.Trade_Date = TDateSell.Trade_Date
    ON buy.Acct_ID = sell.Acct_ID

---

EDIT: after seeing OP's comment I have changed the query

编辑：看到OP的评论后我改变了查询

SELECT 
    buy.Acct_ID, buy.Trade_Date, buy.Brokerage_Transaction_Key, buy.Buy_Sell_Code, buy.Principal_Amt, buy.Security_Quantity, 
    sell.Acct_ID, sell.Trade_Date, sell.Brokerage_Transaction_Key, sell.Buy_Sell_Code, sell.Principal_Amt, sell.Security_Quantity
FROM (
        SELECT wr.*, MIN(TransKey) TransKey -- This is the value of the Sell to be joined
        FROM #TRANSACTIONS_WITH_RANK wr
            LEFT OUTER JOIN (
                SELECT MIN(Brokerage_Transaction_Key) TransKey, Acct_ID
                FROM (
                        SELECT 
                            tr.*, 
                            (
                                SELECT MAX(Brokerage_Transaction_Key) --Purpose is to give outer query value to GROUP on
                                FROM #TRANSACTIONS_WITH_RANK 
                                WHERE Buy_Sell_Code = 'B' 
                                    AND Acct_ID = tr.Acct_ID
                                    AND Brokerage_Transaction_Key < tr.Brokerage_Transaction_Key
                            ) MaxLesserKey
                        FROM #TRANSACTIONS_WITH_RANK tr
                    ) data
                WHERE Buy_Sell_Code = 'S'
                GROUP BY Acct_ID, MaxLesserKey
            ) MinSell
            ON wr.Acct_ID = MinSell.Acct_ID
                AND wr.Brokerage_Transaction_Key < MinSell.TransKey
        WHERE Buy_Sell_Code = 'B'
        GROUP BY wr.Acct_ID, Trade_Date, Brokerage_Transaction_Key, Buy_Sell_Code, Principal_Amt, Security_Quantity
    ) buy
    FULL OUTER JOIN (
        SELECT wr.*, MIN(MinBuy.TransKey) TransKey -- This is the value of the Buy to be joined
        FROM #TRANSACTIONS_WITH_RANK wr
            LEFT OUTER JOIN (
                SELECT MIN(Brokerage_Transaction_Key) TransKey, Acct_ID
                FROM (
                        SELECT 
                            tr.*,
                            (
                                SELECT MAX(Brokerage_Transaction_Key) --Purpose is to give outer query a value to GROUP on
                                FROM #TRANSACTIONS_WITH_RANK
                                WHERE Buy_Sell_Code = 'S'
                                    AND Brokerage_Transaction_Key < tr.Brokerage_Transaction_Key
                            ) MaxLesserKey
                        FROM #TRANSACTIONS_WITH_RANK tr
                    ) data
                WHERE Buy_Sell_Code = 'B'
                GROUP BY Acct_ID, MaxLesserKey
            ) MinBuy
            ON wr.Acct_ID = MinBuy.Acct_ID
                AND wr.Brokerage_Transaction_Key < MinBuy.TransKey
        WHERE Buy_Sell_Code = 'S'
        GROUP BY wr.Acct_ID, Trade_Date, Brokerage_Transaction_Key, Buy_Sell_Code, Principal_Amt, Security_Quantity
    ) sell
    ON buy.TransKey = sell.Brokerage_Transaction_Key
        OR sell.TransKey = buy.Brokerage_Transaction_Key

Basically what this does is grab all Buy(s) and their matching Sell Brokerage_Transaction_Key (TransKey) and does a FULL OUTER JOIN (NULLs out the Buy or Sell side when there are no opposite matching transactions) to the set of Sell(s) and their matching Buy Brokerage_Transaction_Key (TransKey). The TransKey is the smallest Brokerage_Transaction_Key of the opposite Buy_Sell_Code for each group of Buy(s)/Sell(s). This will give you first Sell to first Buy(s) or first Buy to first Sell(s) per group of transactions for a particular Acct_ID. The MaxLesserKey field is there to just to give the TransKey query a value to GROUP on

基本上这样做是抓住所有买入及其匹配的卖出Brokerage_Transaction_Key（TransKey），并在卖出一套卖出时执行FULL OUTER JOIN（当没有相反的匹配交易时为买入或卖出方面为NULL）他们的匹配购买Brokerage_Transaction_Key（TransKey）。 TransKey是每组Buy（s）/ Sell（s）的相反Buy_Sell_Code的最小Brokerage_Transaction_Key。这将为您提供特定Acct_ID的每组交易的首次卖出或首次卖出或首次卖出。 MaxLesserKey字段用于向TransKey查询赋予GROUP on值