Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: need help w complex sql query

Re: need help w complex sql query

From: Isaac Blank <izblank_at_yahoo.com>
Date: Tue, 16 Sep 2003 23:34:57 GMT
Message-ID: <B6N9b.85$Cd1.0@newssvr29.news.prodigy.com>


oops...
SELECT TDATE, PRICE
FROM X.PRICES, CALENDAR
WHERE CONTRACT = 'SPA'
AND Calendar.cal_date = TDATE
AND is_last_business_day_of_month = 'Y';

"Isaac Blank" <izblank_at_yahoo.com> wrote in message news:MKL9b.70$vw.27_at_newssvr29.news.prodigy.com...
> Whenever one deals with issues like weekdays, weekends, and holidays, it
is
> a good idea to create a Calendar table, one of the columns being
> "is_last_business_day_of_month"
>
> Then your query will look like this:
>
> SELECT TDATE, PRICE
> FROM X.PRICES, CALENDAR
> WHERE CONTRACT = 'SPA'
> AND Calendar.cal_date = TDATE
> AND is_last_business_day_of_month ' 'Y';
>
> "Mike Stenzler" <external_at_rxr.com> wrote in message
> news:3F674163.7CA77C71_at_rxr.com...
> > I'm trying to create a view on a large table of price data. The rows I
> > want to belong to the view are of a specific financial instrument and
> > should only be the last business day of the month. The target table has
> > rows for every day, with weekends and holidays being skipped.
> >
> > I'm able to write a SQL query that returns only End Of Month days, but
> > if the EOM falls on a weekend, that month returns nothing -
> >
> > SELECT TDATE, PRICE FROM X.PRICES
> > WHERE CONTRACT = 'SPA' AND
> > TDATE = LAST_DATE(TDATE)
> > ORDER BY TDATE;
> >
> > A snippet of what this will return ...
> > 31-DEC-02 879.82
> > 31-JAN-03 855.7
> > 28-FEB-03 841.15
> > 31-MAR-03 848.18
> > 30-APR-03 916.92
> > 30-JUN-03 974.5
> > 31-JUL-03 990.31
> >
> > Notice that there is no value returned for MAY or AUGUST as the last
> > day of these months fall on the weekend so they don't exist in the
> > target table. What is desired is for 30-MAY-03 and 29-AUG-03 to be
> > returned instead as they are the last business days of the month.
> >
> > Any ideas as to how to re-write this query without procedural language
> > as it must create a view?
> >
> > Mike
> >
> >
>
Received on Tue Sep 16 2003 - 18:34:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US