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: Turkbear <john.greco_at_dot.state.mn.us>
Date: Tue, 16 Sep 2003 12:56:38 -0500
Message-ID: <6kjemvoj1qq6iqjqs7ji45en0mh8v52262@4ax.com>


Mike Stenzler <external_at_rxr.com> wrote:

>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
>

You may need to test for the Day of the week and using that adjust the where clause to use Last_day(tdate) -1 ( if SAT ) or -2 (If SUN) to get the last Friday of the month... Received on Tue Sep 16 2003 - 12:56:38 CDT

Original text of this message

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