Re: need help w complex sql query

From: Mike Stenzler <external_at_rxr.com>
Date: Wed, 17 Sep 2003 11:18:54 -0400
Message-ID: <3F687B5E.F926749D@rxr.com>

Thanks to all for the various and sundry suggestions.

I've resolved the issue with the following query -

CREATE VIEW RESEARCH.SP500_EOM_PRICES AS SELECT TDATE, PRICE FROM X.PRICES WHERE    CONTRACT = 'SPA' AND
   TDATE IN
   ( SELECT MAX( TDATE ) FROM X.PRICES WHERE

      CONTRACT = 'SPA'
      GROUP BY  TO_CHAR( TDATE,  'MM-RRRR' ))

   ORDER BY TDATE; This yields the correct results with the exception of the last (most recent) date which will be the most recent price, not neccessarily the EOM price. This is easily handled programatically by excluding the last date.

Thanks again.

Mike

.

Mike Stenzler 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
Received on Wed Sep 17 2003 - 10:18:54 CDT