Using SQL Server 2012:
A Round Trip is defined as a buy, sell and then another buy within a specified period of time.
Two round-trips would be considered as a buy-sell-buy-sell-buy of an item within the specified period of time.
A round-trip always starts with a buy and ends with a buy.
The specified time period ('x' days) is always anchored by a given [base date] date, and looks back from that date. For example, if the base date is 2015-09-10, the look-back period is 60 days, and the [ROUNDTRIP COUNT] is 3, then the most recent buy should always be on 2015-09-10 and there must be a B-S-B-S-B-S-B in that order on subsequent days within the look-back period.
The pertinent columns are account_number, trade_id, trade_date, trade_type (Buy or Sell).
IF ROUNDTRIP COUNT = 1 then B-S-B
IF ROUNDTRIP COUNT = 2 then B-S-B-S-B
IF ROUNDTRIP COUNT = 3 then B-S-B-S-B-S-B
1. There must be a buy on base date
2. There must be an initial buy within base date - [lookback days].
3. The sequence of trade_types is always B-S-B[[[-S-B]-S-B]...]
4. Round trips are based on the same account number and trade id.
5. It would probably be nice (maybe in a future version to count a B-B[-B] or S-S[-S] sequence as a single buy or sell, but for now we are interested only in the specific sequence of B-S-B...
6. [base date], [roundtrip count] and [lookback days] are parameters that are passed to the procedure.
7. The initial buy must have occurred within [base date] - [lookback days]. If the full sequence of events is B-S-B-S-B, this is two round trips, but the initial buy is outside the range, then it can only be counted as one.
8. For transactions that meet the criteria, I need the account number, trade id, trade type, trade date, along with some other info associated with the specific trade (quantity and price).
Intermediate temp tables are okay, but really don't want to even think about using cursors.