Time Series Data Self-Connection

Question

I need help with what, in my opinion, should be a fairly simple request for self-join. You just need to combine the same start and end time between two records in one record

Let's say that the table has

Time  Event
08:00 Start
09:00 Stop
10:30 Start
10:45 Stop
11:00 Start
11:15 Start
12:00 Stop
12:30 Stop

I need a similar image

StartTime Stoptime
08:00  09:00 
10:30  10:45 
11:00 
11:15  12:00 
       12:30 

Please note that he must find the nearest start or end time. If for some reason the corresponding event does not exist, it should be empty.

Thank,

Answer 1

: . . , LEFT JOIN .

SQL Server :

DECLARE @Events  table (EventTime char(5), EventType varchar(5))

INSERT INTO @Events VALUES ('08:00','Start')
INSERT INTO @Events VALUES ('09:00','Stop')
INSERT INTO @Events VALUES ('10:30','Start')
INSERT INTO @Events VALUES ('10:45','Stop')
INSERT INTO @Events VALUES ('11:00','Start')
INSERT INTO @Events VALUES ('11:15','Start')
INSERT INTO @Events VALUES ('12:00','Stop')
INSERT INTO @Events VALUES ('12:30','Stop')

SELECT
    dt.StartTime, dt.StopTime
    FROM (SELECT
              p.EventTime AS StartTime,CASE WHEN c.EventType!='Stop' THEN NULL ELSE c.EventTime END AS StopTime
                  ,p.EventTime AS SortBy
              FROM @Events          p
                INNER JOIN @Events  c ON p.EventTime<c.EventTime
              WHERE p.EventType='Start'
                  AND c.EventTime=(SELECT MIN(EventTime) FROM @Events WHERE  EventTime>p.EventTime)
          UNION
          SELECT
              NULL AS StartTime,p.EventTime
                  ,p.EventTime AS SortBy
              FROM @Events          p
                INNER JOIN @Events  c ON p.EventTime>c.EventTime
              WHERE p.EventType='STOP'
                  AND c.EventTime=(SELECT MAX(EventTime) FROM @Events WHERE  EventTime<p.EventTime)
                  AND c.EventType='Stop'
         ) dt
    ORDER BY dt.SortBy

:

StartTime StopTime
--------- --------
08:00     09:00
10:30     10:45
11:00     NULL
11:15     12:00
NULL      12:30

(5 row(s) affected)

Answer 2

, SQL- sql-. , Oracle, LEAD() LAG()...

SQL> select * from (
  2      select case when event = 'Start' then t else null end as start_t
  3             , case when next_event = 'Stop' then next_t
  4                    when event = 'Stop' and prev_event = 'Stop' then next_t
  5                    else null end as stop_t
  6      from (
  7          select event
  8                  , t
  9                  , lead (t) over (order by t) as next_t
 10                  , lead (event) over (order by t) as next_event
 11                  , lag (event) over (order by t) as prev_event
 12          from t23
 13          order by t )
 14      )
 15  where start_t is not null
 16  or    stop_t is not null
 17  /

START STOP_
----- -----
08:00 09:00
10:30 10:45
11:00
11:15 12:00
      12:30

SQL>