Can an analytic function do the following? find consective days where a value was over a threshold?

We have table A with the following structure

ID      Date        value
1        6/1/03     10
1       6/2/03     20
1       6/3/03     10
1       6/4/03     4
1       6/5/03     45
1       6/6/03     32
1       6/7/03     22

2        6/1/03     70
2       6/2/03     20
2       6/3/03     5
2       6/4/03     5
2       6/5/03     5
2       6/6/03     20
2       6/7/03     30

We want to find, starting from 6/7/06 number of consecutive days prior to that date that the value exceeded 10.

for exampe

ID 1 3 days 6/7-6-5

ID 2 2 days 6/7-6

Can an analytical function do this? We want to avoid a stored procedure for a variety of reasons (we have 125,000,000 rows to go thru and loading each row and
comparing it to the previous and so forth will be too slow, we hope an AF will be faster) Received on Thu May 11 2006 - 09:44:03 CDT