Re: need help w complex sql query

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 - 17:01:16 CDT