Re: SQL problem enclosed. SELECTing moving window of values...

"Lennart Jonsson" <lennart_at_kommunicera.umea.se> wrote:
> Ahhhh, are you by any chance trying to calculate the cumulative sum? I
dont
> think that is possible within standard sql (but I might be wrong, I
usually am
> :-). There are however vendors that provide OLAP functions for such
purposes.
> With DB2 V7 one can express it aprox as:
>
> select month_name, max(cumulative_sum) from (
> SELECT monthname(changedate) as month_name, sum(1) OVER (
> ORDER BY month(changedate)
> ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
> AS CUMULATIVE_SUM
> from workstat where wostat = 'INPRG'
> and changedate <= '3/1/2000'
> ) as X group by month_name

It _is_ ANSI SQL 99, also implemented (at least) by Oracle 8.1.?? and Teradata

> I have not used this functions before, so there are probably a more
elegant
> solution to the problem.

Definitly not.
Using a cursor is slower, but much slower is the only other possibility: a cross join

Dieter Received on Thu Nov 08 2001 - 04:51:54 CST