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@compulab.co.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
