join on next row

From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
To: pgsql-sql(at)postgresql(dot)org
Subject: join on next row
Date: 2006-06-18 11:14:33
Message-ID: e7396t$18o8$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Broersma Jr 2006-06-18 15:28:46 Re: any additional date_time functions?
Previous Message Bruno Wolff III 2006-06-18 09:54:50 Re: any additional date_time functions?