This can be done more efficiently with a simple ROW_NUMBER implementation.

😎

The problem with your solution is that it scans the table 1+(2 x number of rows) times, fear it will be very inefficient on larger sets.

Single table scan solution

;with SAMPLE_DATA as (

select 'start' [eventtype],cast('2016-05-17 08:00:00' as datetime) [eventdatetime]

union select 'start',cast('2016-05-17 09:10:00' as datetime)

union select 'start',cast('2016-05-17 09:40:00' as datetime)

union select 'stop',cast('2016-05-17 09:50:00' as datetime)

union select 'stop',cast('2016-05-17 09:12:00' as datetime)

union select 'stop',cast('2016-05-17 08:10:00' as datetime)

union select 'start',cast('2016-05-17 09:40:00' as datetime)

union select 'stop',cast('2016-05-17 09:50:00' as datetime)

union select 'stop',cast('2016-05-17 09:12:00' as datetime)

union select 'start',cast('2016-05-17 10:00:00' as datetime)

union select 'stop',cast('2016-05-17 10:50:00' as datetime)

union select 'start',cast('2016-05-17 10:40:00' as datetime)

union select 'stop',cast('2016-05-17 11:50:00' as datetime)

)

,GROUPED_DATA AS

(

SELECT

(ROW_NUMBER() OVER

(

ORDER BY SD.eventdatetime

) / 2) +

CASE

WHEN SD.eventtype = 'start' THEN 1

ELSE 0

END AS GRP

,SD.eventtype

,SD.eventdatetime

FROM SAMPLE_DATA SD

)

SELECT

MAX(CASE WHEN GB.eventtype = 'start' THEN GB.eventdatetime END) AS starttime

,MAX(CASE WHEN GB.eventtype = 'stop' THEN GB.eventdatetime END) AS endtime

FROM GROUPED_DATA GB

GROUP BY GB.GRP;

Output

starttime endtime

----------------------- -----------------------

2016-05-17 08:00:00.000 2016-05-17 08:10:00.000

2016-05-17 09:10:00.000 2016-05-17 09:12:00.000

2016-05-17 09:40:00.000 2016-05-17 09:50:00.000

2016-05-17 10:00:00.000 2016-05-17 10:50:00.000

2016-05-17 10:40:00.000 2016-05-17 11:50:00.000