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

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 Received on Wed Nov 07 2001 - 08:07:23 CST