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.
LVL 15
Doug BishopDatabase DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Very well described.  Some sample data would be very helpful.  It will take me a while to create sample data on my own.
Scott PletcherSenior DBACommented:
I think the code below should at least be very close.  Without specific data, I can't test it any further.

CREATE TABLE tablename (
    [account number] varchar(30) NULL,
    [trade id] int NULL,
    trade_date datetime NULL,
    trade_type char(1) NULL
    )
INSERT INTO tablename
SELECT '#1', 1, '20150910', 'B' UNION ALL
SELECT '#1', 1, '20150901', 'S' UNION ALL
SELECT '#1', 1, '20150827', 'B' UNION ALL
SELECT '#1', 1, '20150825', 'S'

SELECT * FROM tablename

--parameter setup/values------------------------

DECLARE @base_date datetime
DECLARE @lookback_days int
DECLARE @roundtrip_count int
SET @base_date = '20150910'
SET @lookback_days = 60
SET @roundtrip_count = 1

--main code using passed params---------------------------------

SET @base_date = DATEADD(DAY, DATEDIFF(DAY, 0, @base_date), 0) --strip time, to be sure

SELECT *
FROM (
    SELECT tn.[account number], tn.[trade id],
        (SELECT tn2.trade_type+''
         FROM tablename tn2
         WHERE tn2.[account number] = tn.[account number] AND
               tn2.[trade id] = tn.[trade id] AND
               tn2.trade_date >= DATEADD(DAY, -@lookback_days, @base_date) AND
               tn2.trade_date < DATEADD(DAY, 1, @base_date)
         ORDER BY tn2.trade_date
         FOR XML PATH('')
         ) AS trade_pattern
    FROM tablename tn
    WHERE
        tn.trade_date >= @base_date AND
        tn.trade_date < DATEADD(DAY, 1, @base_date) AND
        tn.trade_type = 'B'
) AS derived
WHERE SUBSTRING(trade_pattern, CASE WHEN trade_pattern LIKE 'B%' THEN 1 ELSE 2 END, 100) LIKE 'B' + REPLICATE('SB', @roundtrip_count) + '%'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Doug BishopDatabase DeveloperAuthor Commented:
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.
Scott PletcherSenior DBACommented:
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.
Doug BishopDatabase DeveloperAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.