Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Obtain largest timespan where sum of values is below threshold

Dan Scott GuestAug 27 2010 — edited Mar 8 2011
Hi,

I asked this question in the general forum a while ago and it was suggested that I post here too.

I'm trying to obtain the 'largest timespan where sum of values is below threshold'. More details available here:

1119232

Here's the sample data:
create table outputs (output_date date, quantity number);

insert into outputs (output_date, quantity) values (to_date ('31-JUL-3000 09.00.00', 'DD-MON-YYYY HH24.MI.SS'), 10);
insert into outputs (output_date, quantity) values (to_date ('31-JUL-3000 12.00.00', 'DD-MON-YYYY HH24.MI.SS'), 100);
insert into outputs (output_date, quantity) values (to_date ('31-JUL-3000 14.00.00', 'DD-MON-YYYY HH24.MI.SS'), 1);
insert into outputs (output_date, quantity) values (to_date ('31-JUL-3000 18.00.00', 'DD-MON-YYYY HH24.MI.SS'), 1);
insert into outputs (output_date, quantity) values (to_date ('31-JUL-3000 20.30.00', 'DD-MON-YYYY HH24.MI.SS'), 1);
insert into outputs (output_date, quantity) values (to_date ('31-JUL-3000 22.00.00', 'DD-MON-YYYY HH24.MI.SS'), 10);
insert into outputs (output_date, quantity) values (to_date ('01-AUG-3000 02.00.00', 'DD-MON-YYYY HH24.MI.SS'), 10);
The output should be (for a threshold of 9):
31-JUL-3000 14.00.00,	31-JUL-3000 20.30.00,	3,	3
The first two columns are the start and end of the window, the final 2 columns are the total within the window (below the threshold) and the number of entries. As a separate, but related problem, I'd also like to obtain the largest gap between measurements within the window.

Thanks,

Dan Scott
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 27 2010
Added on Aug 27 2010
8 comments
1,599 views