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