Use = instead of < . On Wed, Jun 30, 2010 at 10:44 AM, aimoux <xw2...@gmail.com> wrote:
> 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 > -- 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
