Re: SQL problem enclosed. SELECTing moving window of values...
Date: Wed, 07 Nov 2001 09:07:23 -0500
Message-ID: <mlfiutovk1380evsrev5bks7mjkovqnecl_at_4ax.com>
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 - 15:07:23 CET