Re: join on next row

[Harald Fuchs <hf0406x(at)protecting(dot)net>]

In article <e780u8$1h5e$1(at)news(dot)hub(dot)org>,
Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:

> I want my query resultset to be
> Employee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2)
> Where Event(2) is the first event of the employee that took place
> after the other event.

> Example
> EventID    Employee    EventDate    EventTime    EventType
> 1    John        6/15/2006    7:00        A
> 2    Frank        6/15/2006    7:15        B
> 3    Frank        6/15/2006    7:17        C
> 4    John        6/15/2006    7:20        C
> 5    Frank        6/15/2006    7:25        D
> 6    John        6/16/2006    7:00        A
> 7    John        6/16/2006    8:30        R

> Expected Results
> John, 6/15/2006, 7:00, A, 7:20, C
> Frank, 6/15/2006, 7:15, B, 7:17, C
> Frank, 6/15/2006, 7:17, C, 7:25, D
> John, 6/16/2006, 7:00, A, 8:30, R

> To get this result set it would have to be an inner join on employee
> and date where the second event time is greater then the first. But I
> don't want the all of the records with a greater time, just the first
> event after.

You can filter the others out by an OUTER JOIN:

  SELECT e1.Employee, e1.EventDate,
         e1.EventTime, e1.EventType,
         e2.EventTime, e2.EventType
  FROM events e1
  JOIN events e2 ON e2.Employee = e1.Employee
                AND e2.EventDate = e1.EventDate
                AND e2.EventTime > e1.EventTime
  LEFT JOIN events e3 ON e3.Employee = e1.Employee
                     AND e3.EventDate = e1.EventDate
                     AND e3.EventTime > e1.EventTime
                     AND e3.EventTime < e2.EventTime
  WHERE e3.EventID IS NULL
  ORDER BY e1.EventDate, e1.EventTime