Self join of time series data

Question

I need some help with what I think should be a fairly simple self join query. It just needs to combine the matching start time and end times from two records into one record

Say I have the following in a table

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 want a view like this

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

Notice it should find the closest matching start or end time. If for some reason there is not a matching event it should leave it blank.

Thanks,

Answer 1

disclaimer: I would seriously consider a different table design. Joining on dates and or times is never a good idea. If you had an ID of the event that is starting, you could easily LEFT JOIN to try to find the matching end of that event.

For SQL Server try:

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

OUTPUT:

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

I'm guessing that you are using the SQL tag to mean sql-server. But in case future seekers are interested in an Oracle solution this problem can be solved with the analytic LEAD() and LAG() functions...

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>