Calculate "Round Trips" in T-SQL
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
General rules:
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.
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
General rules:
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.
Last Comment
Scott Pletcher
Very well described. Some sample data would be very helpful. It will take me a while to create sample data on my own.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Works like a charm. Only change was to change order in the FOR XML to descending because we start with the premise that the most recent trade_type is a 'B' was work backwards from there. As a result, the CASE in the SUBSTRING is not needed, nor is the date range test in the outer query (only where tn.trade_date_timestamp = @base_date AND tn.codes = 'B'). Other than that, seems good so far.
Thinking about wrapping the FOR XML query into a function that will resolve multiple contiguous B's and S's so that BBBSSSSBBSSBBB will look like BSBSB.
Thinking about wrapping the FOR XML query into a function that will resolve multiple contiguous B's and S's so that BBBSSSSBBSSBBB will look like BSBSB.
The WHERE might still make it more efficient by ignoring those acct & tran ids where you don't have a B on the end date.
ASKER
Working backwards from @base_date, that code must always be a 'B'. So actually, although I wrote the original spec as must end with a 'B', in reality, a better way of explaining it is that the most recent transaction must have occurred on @base_date and must have been a 'B'. In that way, we exclude having to look at the possibility of an initial 'S', which is really a trailing 'S' if you are working backwards. Hope that makes sense.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
