Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: need help w complex sql query
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