need help w complex sql query

From: Mike Stenzler <external_at_rxr.com>
Date: Tue, 16 Sep 2003 12:59:15 -0400
Message-ID: <3F674163.7CA77C71@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 - 11:59:15 CDT