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

In article <DsgG7.16479$xS6.25859_at_www.newsranger.com>, Lennart Jonsson says...
>
>In article <mlfiutovk1380evsrev5bks7mjkovqnecl_at_4ax.com>, Remo Williams says...
>>
>>Table
>>--------
>>wonum status changedate
>>[int varchar(6) smalldatetime ]
>>--------------------------------------------------------------
>>100 WSCH 2000-01-01 00:00:00
>>200 WSCH 2000-01-01 00:00:00
>>300 WSCH 2000-01-01 00:00:00
>>400 WSCH 2000-01-01 00:00:00
>>500 WSCH 2000-01-01 00:00:00
>>100 INPRG 2000-01-01 00:00:00
>>200 INPRG 2000-01-15 00:00:00
>>300 INPRG 2000-02-01 00:00:00
>>200 COMP 2000-02-11 00:00:00
>>400 INPRG 2000-03-01 00:00:00
>>500 INPRG 2000-03-31 00:00:00
>>100 COMP 2000-05-01 00:00:00
>>300 COMP 2000-05-01 00:00:00
>>400 COMP 2000-05-01 00:00:00
>>
>>How about a simpler way of expressing this:
>>
>>select wo1.wonum, wo1.status
>>from wostatus wo1
>>where wo1.status = 'INPRG'
>>and wo1.changedate <= '3/1/2000'
>>and wo1.wonum not in
>>(select wo2.wonum from wostatus wo2
>> where wo2.status in ('COMP','CLOSE','CAN')
>> and changedate <= '3/1/2000')
>>
>>which yields all the 'INPRG' records up to March 1st.
>>
>>I'm trying to do a 12-month view of records marked INPRG by the first
>>of each month, to make a pretty histogram that will be ignored by some
>>manager. Ideally, I can slide that month value and come up with a
>>select that gives me outut similar to:
>> jan 35
>> feb 55
>> mar 47 ...
>>
>>This might be too tricky for SQL, but I don't want to use a cursor.
>>There could be thousands and thousands of rows.
>>
>>-Remo
>

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

I have not used this functions before, so there are probably a more elegant solution to the problem. Sorry for the inapropiate posts earlier.

/Lennart Received on Wed Nov 07 2001 - 22:32:58 CET