This might give you an approach to the problem ...
CREATE TABLE stocks AS
SELECT trunc(SYSDATE) the_date, 10 price FROM DUAL
UNION ALL
SELECT trunc(SYSDATE) - 17 the_date, 5 price FROM DUAL
UNION ALL
SELECT trunc(SYSDATE) - 27 the_date, 7 price FROM DUAL
UNION ALL
SELECT trunc(SYSDATE) - 30 the_date, 9 price FROM DUAL;
SELECT SUM( CASE WHEN the_date < trunc(SYSDATE) - 5 THEN price ELSE 0 END )
day05,
SUM( CASE WHEN the_date < trunc(SYSDATE) - 20 THEN price ELSE 0 END )
day20,
SUM( CASE WHEN the_date < trunc(SYSDATE) - 30 THEN price ELSE 0 END )
day30
FROM stocks;
DAY05 DAY20 DAY30
---------- ---------- ----------
21 16 0
1 row selected.
On Wed, Jun 30, 2010 at 7:51 AM, aimoux <xw2...@gmail.com> wrote:
> Hi,
>
> I am fairly new to PL/SQL. And I am learning to create an analytical
> function that allows me to fetch t+5*n days' data. For instance, today
> is 20100630, what are S&P 500 closing prices 5, 10, 15, 20, 25,
> 30, ....up to 300 days later? I do have a table that is like a
> calendar, which makes it easier to pick only trading dates when the
> stock market is open. In the output, I will have one row (today's
> date), with 300/5=60 columns (5, 10, 15, 20, 25...300 days later).
> Could anyone help me with this analytical function? I have never
> written one before.
>
> Thank you very much! I truly appreciate it.
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
