Re: join on next row

I would use a stored procedure or function for this. You order your results
first by employee and then event date and finally even time. Then you
create a new result set from the first and return that.

That would probably be the most straight forward approach.

You could also try doing some thing like this (I have not tested it and so
cannot vouch for its syntax but it should lead you close to another
solution):

select
eventjoin.employee,
eventjoin.eventdate,
eventjoin.eventtime,
eventjoin.eventtype,
eventjoin.maxeventtime,
e3.eventtype
from (
select
e1.employee,
e1.eventdate,
e1.eventtime,
e1.eventtype,
max(e2.eventtime) as maxeventtime
from events e1
inner join events e2 on (
e1.employee = e2.employee
and e1.eventDate = e2.eventDate
and e1.eventTime > e2.eventTime
)
order by
e1.employee
e1.eventDate
e1.eventTime
) eventjoin
inner join event e3 on (
e3.employee = eventjoin.employee
and e3.eventdate = eventjoin.eventdate
and e3.eventtime = eventjoin.maxeventtime
);

Who knows what the performance of this will be. I would highly recommend
you have employee in a separate table if you do not already.

-Aaron Bono

On 6/18/06, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
>
> I am having brain freeze right now and was hoping someone could help me
> with a (fairly) simple query.
>
> I need to join on the next row in a similar table with specific criteria.
>
> I have a table with events per employee.
> I need to have a query that gives per employee each event and the event
> after it if it happened on the same day.
>
> The Events table structure is:
>
> EventID
> Employee
> EventDate
> EventTime
> EventType
>
> 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.
>
> Thank You
> Sim