Skip to Main Content

Oracle Database Discussions


For appeals, questions and feedback about Oracle Forums, please email Please ask technical questions in the appropriate category. Thank you!

Obtain largest timespan where sum of values is below threshold

Dan Scott GuestAug 19 2010 — edited Aug 27 2010

For a set of hourly values, i'm trying to obtain the biggest time span, where the sum of the values is less than a threshold.

i.e. for this data:

31-JUL-3000 09.00.00 10
31-JUL-3000 12.00.00 100
31-JUL-3000 14.00.00 1
31-JUL-3000 18.00.00 1
31-JUL-3000 20.30.00 1
31-JUL-3000 22.00.00 10
01-AUG-3000 02.00.00 10

The output would be in a form like:
first_entry, last_entry, total, number_of_entries

e.g. threshold '9':
31-JUL-3000 14.00.00, 31-JUL-3000 20.30.00, 3, 3

threshold '14' would give:
31-JUL-3000 14.00.00, 31-JUL-3000 22.00.00, 13, 4

I don't think I can easily use an analytic function, because I need the window to be the sum of the data. I could write new code for each window size that I'd like to check, but this feels messy. I could also use a subquery to calculate the totals for all windows, but that will become very large, very quickly.

I'm guessing that I might have to use the MODEL clause - does anyone have any suggestions for how to get started? I'm using Oracle 11gR2.


Dan Scott
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 24 2010
Added on Aug 19 2010