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

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

Let me see if I get this straight. You want to count rows where status = 'INPRG' and there is no rows with the same wonum and another status, and group them by month. Is that correct?

If my assumtion is true I would try something like:

select monthname(changedate), count(1) from wor kstat w where wostat = 'INPRG' and changedate <= '3/1/2000' and not exists (sel ect 1 from workstat where wonum = w.wonum and wostat <> 'INPRG' and changedate <= '3/1/2000') group by monthname(changedate)

with your sample data posted earlier, no rows are returned since there exists rows with wostat = 'WSCH' for all wonum (where wostat = 'INPRG').

you also mention something about the first in each month. If you only want to count rows the first each month exchange count(1) with something like:

count(case when day(changedate)=1 then 1 else null end)

/Lennart Received on Wed Nov 07 2001 - 21:03:47 CET