On 20 Jun 2006 18:20:55 +0200, Harald Fuchs <hf0406x@xxxxxxxxxxxxxx> wrote:
In article <e780u8$1h5e$1@xxxxxxxxxxxx>,
Sim Zacks <sim@xxxxxxxxxxxxxx> writes:
> 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
This will only give the correct answer if the next event is on the
same day. This does not match the problem as stated. The actual
answer is more complex than it looks (in < pg 8.2). In pg 8.2, you
can make:
SELECT e1.Employee, e1.EventDate,
e1.EventTime, e1.EventType,
e2.EventTime, e2.EventType
FROM events e1
JOIN events e2 ON
(e2.Employee, e2.EventDate, e2.EventTime) >
(e1.Employee, e1.EventDate, e1.EventTime)
LEFT JOIN events e3 ON
(e3.Employee, e3.EventDate, e3.EventTime) >
(e1.Employee, e1.EventDate, e1.EventTime) AND
e3.EventTime < e2.EventTime
WHERE e3.EventID IS NULL
ORDER BY e1.EventDate, e1.EventTime
if you only want answers that match the same date as the selected
event, harald's answer is correct. to get the correct answer in 8.1
and down you must make a monster of a sql statement ;)
merlin
