I'm running SQL Server 2016 and I have a set of data that looks like this
Name Time Type
CL1 2018-05-22 10:50:00 -04:00 SampleAndTrade
CL1 2018-05-22 11:00:00 -04:00 TradeOnly
CL1 2018-05-22 11:10:00 -04:00 TradeOnly
CL1 2018-05-22 11:20:00 -04:00 TradeOnly
CL1 2018-05-22 14:10:00 -04:00 SampleAndTrade
CL1 2018-05-22 14:20:00 -04:00 TradeOnly
CL1 2018-05-22 14:30:00 -04:00 TradeOnly
CL1 2018-05-22 14:40:00 -04:00 TradeOnly
CL1 2018-05-22 14:50:00 -04:00 TradeOnly
HO1 2018-05-22 10:50:00 -04:00 SampleAndTrade
HO1 2018-05-22 11:00:00 -04:00 TradeOnly
HO1 2018-05-22 11:10:00 -04:00 TradeOnly
HO1 2018-05-22 11:20:00 -04:00 TradeOnly
HO1 2018-05-22 14:10:00 -04:00 SampleAndTrade
HO1 2018-05-22 14:20:00 -04:00 TradeOnly
HO1 2018-05-22 14:30:00 -04:00 TradeOnly
HO1 2018-05-22 14:40:00 -04:00 TradeOnly
HO1 2018-05-22 14:50:00 -04:00 TradeOnly
A trade period starts with a SampleAndTrade and ends with a TradeOnly (before the next SampleAndTrade). You will always have at least one TradeOnly After a SampleAndTrade. The interval will always be uniform across a single trade. I would like to transpose this data so that it looks like this:
Name StartTime EndTime IntervalMin
CL1 10:50 11:20 10
CL1 14:10 14:50 10
HO1 10:50 11:20 10
HO1 14:10 14:50 10
I really can't figure out how to partition the data correctly to roll up over trade cycles. Could anyone give me a hint as to how I could achieve this?
SELECT
Name,
MIN(Time) AS StartTime,
MAX(Time) AS EndTime,
MIN(Diff) AS IntervalMin
FROM
(
SELECT
*,
SUM(CASE WHEN type = 'SampleAndTrade' THEN 1 END)
OVER (PARTITION BY Name
ORDER BY Time
)
AS GroupID,
DATEDIFF(
MINUTE,
LAG(Time)
OVER (PARTITION BY Name
ORDER BY Time
),
Time
)
AS Diff
FROM
yourTable
)
AS summary
GROUP BY
Name,
GroupID