Pfthb

I am trying to create a dataset with all Starts to the nearest Stop.
The problem is that number of starts between the 1st Start and the next Stop varies.

The source dataset looks like this:

RowNum Timestamp Action 
==============================
 1 01/01/18 12:00 Start 
 2 01/01/18 01:00 Start
 3 01/01/18 02:00 Stop
 4 01/01/18 03:00 Start
 5 01/01/18 05:00 Stop
 6 01/01/18 13:00 Start
 7 01/01/18 15:00 Start
 8 01/01/18 17:00 Start
 9 01/01/18 21:00 Stop

I want my final outcome to be something like this:

Start Stop
================================
01/01/18 12:00 01/01/18 02:00
01/01/18 03:00 01/01/18 05:00
01/01/18 13:00 01/01/18 21:00

Or even if it's there is a record for every Start to the nearest Stop that would be great as well.

Thank you so much for any guidance.

You can use lag and ceiling functions as :

select max(Start) as Start, max(Stop) as Stop
 from
 (
 select row_number() over ( order by rownum ) as rn,
 ( case when action = 'Start' then q.timestamp end ) as Start,
 ( case when action = 'Stop' then q.timestamp end ) as Stop
 from
 (
 select t.*, 
 lag(action) over (order by rownum) as lg
 from tab t
 ) q
 where q.action != coalesce(lg,'Stop')
 ) r
 group by ceiling(rn*.5) 
 order by ceiling(rn*.5);

 Start Stop
01.01.2018 12:00:00 01.01.2018 02:00:00
01.01.2018 03:00:00 01.01.2018 05:00:00
01.01.2018 13:00:00 01.01.2018 21:00:00

Rextester Demo

P.S. For every binary pair of steps we determine START and STOP rows where one of them non-null, while the other member of pair is null. Because of this logic I needed mod(...,2) for row numbers and satisfied that by ceil(rn*.5) which yields 1 for both 1 or 2, 2 for both 3 and 4, and 3 for 5 and 6, respectively, i.e. nearest consecutive upper integer.

You can utilize a Cumulative Min to find the next "Stop" event:

select *,
 min(case when Action = 'Stop' then Timestamp end) -- next Stop
 over (--partition by ???
 order by Timestamp
 rows between current row and unbounded following) as Stop
from tab

Based on that it's a simple aggregation:

with cte as
 ( select *,
 min(case when Action = 'Stop' then Timestamp end) -- next Stop
 over (--partition by ???
 order by Timestamp
 rows between current row and unbounded following) as Stop
 from tab
 )
select
 min(Timestamp) as start, 
 Stop
from cte
group by Stop
order by 1

Firstly Get a stop for every start in the table

select
A.Timestamp as Start
min(B.Timestamp) as Stop
from
 mytable A 
left join 
 mytable B
 on A.Action = 'Start' 
 and B.Action = 'Stop'
 and A.Timestamp < B.Timestamp
group by A.Timestamp

You can then use that result (Alias table R1 int the below query) to get your final table

select
min(Start),
Stop
from
(
select
A.Timestamp as Start
min(B.Timestamp) as Stop
from
 mytable A 
left join 
 mytable B
 on A.Action = 'Start' 
 and B.Action = 'Stop'
 and A.Timestamp < B.Timestamp
group by A.Timestamp
) as R1
group by Stop

Solution plan:

Scenario A is when you want to see a Stop for all Starts: find the earliest Stop that is after a Start.

Scenario B is when you want to see a Stop for the earliest/latest Start only: first get a set from the result of Senario A, then find the earliest/latest Start that is before a Stop.

This solution does not consider when there are duplicates and you want to keep them in the result - that would involve a third field like RowNum.

One possible implementation:

DECLARE @Table TABLE (
 Timestamp DATETIME,
 Action VARCHAR(5)
 )

INSERT @Table
VALUES
 ('01/01/18 12:00', 'Start'),
 ('01/01/18 01:00', 'Start'),
 ('01/01/18 02:00', 'Stop'),
 ('01/01/18 03:00', 'Start'),
 ('01/01/18 05:00', 'Stop'),
 ('01/01/18 13:00', 'Start'),
 ('01/01/18 15:00', 'Start'),
 ('01/01/18 17:00', 'Start'),
 ('01/01/18 21:00', 'Stop'),
 ('01/01/18 22:00', 'Start')

SELECT * FROM @Table WHERE Action = 'Start' ORDER BY Timestamp
SELECT * FROM @Table WHERE Action = 'Stop' ORDER BY Timestamp


-- Scenario A:
SELECT Starts.Timestamp as Start, MIN (Stops.Timestamp) as Stop
FROM
(SELECT * FROM @Table WHERE Action = 'Start') as Starts
LEFT OUTER JOIN
(SELECT * FROM @Table WHERE Action = 'Stop') as Stops
on Stops.Timestamp >= Starts.Timestamp
GROUP BY Starts.Timestamp
ORDER BY Starts.Timestamp

-- Scenario B:
-- same block as above with a temp table to hold the results
SELECT Starts.Timestamp as Start, MIN (Stops.Timestamp) as Stop
INTO #allstops
FROM
(SELECT * FROM @Table WHERE Action = 'Start') as Starts
LEFT OUTER JOIN
(SELECT * FROM @Table WHERE Action = 'Stop') as Stops
on Stops.Timestamp >= Starts.Timestamp
GROUP BY Starts.Timestamp

SELECT allstops.Start, LatestStart.Stop
FROM #allstops as allstops
LEFT OUTER JOIN (
 SELECT MIN (Start) as Start, Stop -- this returns the earliest Start, switch to MAX to get the latest
 FROM #allstops
 GROUP BY Stop
 ) as LatestStart
on allstops.Start = LatestStart.Start