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

>Is the following somewhere near what you wish for?
>
>select x.m1, x.m2, count(1) as cum_sum from (select distinc
>t monthname(a.changedate) as m1, month(a.changedate) as m2, b.* from wostatus a
>, wostatus b where a.status = b.status and a.status = 'INPRG' and a.changedate
>>= b.changedate and a.changedate <= '3/1/2000' and b.changedate <= '3/1/2000')
>as x group by x.m1, x.m2 order by x.m2

Nearly... but the business logic says that as of 5/1/2000, I'd only have one row marked as INPRG. So, in essence, the idea I wish to express in pseudo-SQL is:

	-select INPRG rows with max(changedate);
	-of those selected, check wonums for CLOSE-CAN-COMP rows with 	
	 changedates greater than the INPRG row;
	-the rows unmatched to the CLOSE-COMP-CAN rows are the ones 
	 currently INPRG;
	-the window "slides" by moving changedate month-by-month, so those
	 rows in March may be legally missing in April, May, subsequent 
	 months.

It's a tough problem to solve without resorting to programmatic solutions using loops or cursors. This is correct for a month at random:

select wo1.wonum
from wostatus wo1
where wo1.status = 'INPRG'
 and wo1.changedate < '5/1/2000'
 and wo1.wonum not in
(select wo2.wonum
 from wostatus wo2
 where wo2.status in ('CLOSE','CAN','COMP')  and wo1.changedate < wo2.changedate
 and '5/1/2000' >= wo2.changedate)

All I need to do is keep changing the date and save each month's count (which go go up or down month-to-month), but I don't know how to do that through SQL without making it a loop.

I suppose I could write twelve SQL statements and UNION the results in some way so I get Jan...Dec counts in one row, but that sounds like it would be ugly.

And let me just take this time to thank you for helping with the problem, Lennart and Dieter. Much appreciated.

-Remo Received on Fri Nov 09 2001 - 16:03:32 CET