0

I have a set of date values in Oracle that I am doing an ORDER BY START_TIME, STOP_TIME as in the data sample below. The column labeled NEXT_START_TIME, as you can see, is retrieved through the LEAD() function.

START_TIME      STOP_TIME       NEXT_START_TIME
6/13/2013 5:19  6/13/2013 5:34  6/13/2013 5:19   -- no delay, OK
6/13/2013 5:19  6/13/2013 6:23  6/13/2013 5:39
6/13/2013 5:39  6/13/2013 6:04  6/13/2013 6:23   -- delay? wrong
6/13/2013 6:23  6/13/2013 7:32  6/13/2013 9:18
6/13/2013 9:18  6/13/2013 9:50  6/13/2013 9:44
6/13/2013 9:44  6/13/2013 10:01

This data represents assigned jobs. The difficult task I have is, in the most efficient manner, how to determine if there was any delay in assigning a job. The first entry represents no delay since the NEXT_START_TIME came before the STOP_TIME. Only in cases where the NEXT_START_TIME came after the STOP_TIME can we consider there to be a delay (as in the third row).
The wrinkle is that even though the third row would seem to indicate a delay, it's not correct. Even though the NEXT_START_TIME was 6:23 it needs to be aware of the fact that a previous job was still in progress (see the two highlighted items).
Is there a way to accomplish this without O(N), or worse, iterations? Right now the use of the LEAD() function is very efficient.
Please ask for clarifications if needed since I realize this may be difficult to understand.

McArthey
  • 1,614
  • 30
  • 62

1 Answers1

3

Instead of lead(), you want a cumulative max. Try using this instead of lead():

select START_TIME, STOP_TIME,
       max(stop_time) over (order by start_time) as MaxCumStopTime
from t

The order by clause instructs Oracle to make this a cumulative statement, which will take the max of any time up to this row.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Brilliant! I didn't realize the MAX() would work in that manner. It seems to take the MAX() up to and including the transition row (where the start_time changes). – McArthey Jun 27 '13 at 17:28
  • Is it possible to leave NULL values in the STOP_TIME column as NULL and not "overwrite" them with the MAX() value? – McArthey Jun 28 '13 at 12:58