Retreiving future record based on price movement

Hi,

I have a table of closing prices and I want to write a query that returns for each row the EARLIEST FUTURE date where the closing price has gone up at least 20% from the current row.

I want to search only future dates in the next month (or next 20 rows would be OK)

I thought this could be done using analytic functions but can't seem to see how?
The problem I see is that the window function needs to consider both the date and the price movement.

Here is a sample data:

CREATE TABLE prices
AS

SELECT TO_DATE('28-DEC-2001') day, 1.9  close_price FROM dual UNION ALL
SELECT TO_DATE('31-DEC-2001') day, 1.3  close_price FROM dual UNION ALL
SELECT TO_DATE( '2-JAN-2002') day, 1    close_price FROM dual UNION ALL
SELECT TO_DATE( '3-JAN-2002') day, 1.1  close_price FROM dual UNION ALL

SELECT TO_DATE( '4-JAN-2002') day, 1.05 close_price FROM dual UNION ALL

SELECT TO_DATE( '7-JAN-2002') day, 1.2 close_price FROM dual UNION ALL

SELECT TO_DATE( '8-JAN-2002') day, 1.25 close_price FROM dual UNION ALL

SELECT TO_DATE( '9-JAN-2002') day, 1.55 close_price FROM dual UNION ALL

SELECT TO_DATE('10-JAN-2002') day, 1.35 close_price FROM dual UNION ALL

SELECT TO_DATE('11-JAN-2002') day, 1.6 close_price FROM dual UNION ALL

SELECT TO_DATE('14-JAN-2002') day, 1.7 close_price FROM dual

This seems like a curly one. Hope you can help.

Many thanks,
Mark. Received on Thu Mar 02 2006 - 06:54:49 CST