Re: need help w complex sql query

"Mike Stenzler" <external_at_rxr.com> a écrit dans le message de 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
>

This is an hint of what you can do:

SQL>select sysdate, last_day(sysdate),
  2 trunc(last_day(sysdate),'DAY')+4,
  3 least(last_day(sysdate),trunc(last_day(sysdate),'DAY')+4)   4 from dual;

SYSDATE LAST_DAY(S TRUNC(LAST LEAST(LAST ---------- ---------- ---------- ---------- 16/09/2003 30/09/2003 03/10/2003 30/09/2003

1 ligne sélectionnée.

SQL>select to_date('05/08/2003','DD/MM/YYYY'), last_day(to_date('05/08/2003','DD/MM/YYYY')),   2 trunc(last_day(to_date('05/08/2003','DD/MM/YYYY')),'DAY')+4,   3
least(last_day(to_date('05/08/2003','DD/MM/YYYY')),trunc(last_day(to_date('05/08/2003','DD/MM/YYYY') ),'DAY')+4)
  4 from dual;

TO_DATE('0 LAST_DAY(T TRUNC(LAST LEAST(LAST ---------- ---------- ---------- ---------- 05/08/2003 31/08/2003 29/08/2003 29/08/2003

1 ligne sélectionnée.

I'm a french man, so date format is DD/MM/YYYY, first day of the week is Monday and Friday is Monday+4.
If your first day of the week is Sunday, you have to slighty modify this query.

Hope this helps
Michel Cadot Received on Tue Sep 16 2003 - 14:15:20 CDT