It would have been quite easy if done in Oracle's 'lateral view'
feature. But I think it is achievable in standard SQL too; using
subqueries in the select-clause.
Try something like this:
select
Employee, EventDate,
EventTime as e1_time,
EventType as e1_type,
( select
EventTime
from
Events
where Employee = O.Employee
and EventDate = O.EventDate
and EventTime > O.EventTime
limit 1
)as e_time_1,
( select
EventType
from
Events
where Employee = O.Employee
and EventDate = O.EventDate
and EventTime > O.EventTime
limit 1
)
from
Events
Hope it helps...
Regards,
Gurjeet.
On 6/20/06, Sim Zacks <sim@xxxxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
