Thanks a lot Michael. What my boss really wants is the data on day 5, day 10, day 15...etc. not the sum. How to get around that?
On Jun 30, 1:34 pm, Michael Moore <michaeljmo...@gmail.com> wrote: > 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
