I have data that looks like this:
我的数据看起来像这样:
股票买卖
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
0
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
(NULL
s 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值
#1
0
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
(NULL
s 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值