В своем вопросе вы упоминаете, что первая покупка (ы) соответствует первой продаже (ям), но ваш пример вывода, похоже, игнорирует эту часть. Вот пример того, как это сделать, если вы хотите сопоставить первую покупку(и) с первой продажей на основе 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: увидев комментарий 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
По сути, это захватывает все покупки и их соответствующие продажи Brokerage_Transaction_Key
(TransKey
) и выполняет FULL OUTER JOIN
(NULL
s вне стороны покупки или продажи, когда нет противоположных совпадающих транзакций) к набору продаж и их соответствия Купить Brokerage_Transaction_Key
(TransKey
). TransKey
— это наименьшее Brokerage_Transaction_Key
из противоположных Buy_Sell_Code
для каждой группы покупок/продаж. Это даст вам первую продажу для первой покупки или первую покупку для первой продажи для группы транзакций для определенного Acct_ID
. Поле MaxLesserKey
предназначено только для того, чтобы присвоить запросу TransKey
значение GROUP
на
person
Kidiskidvogingogin
schedule
13.02.2016