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

From: Remo Williams <remowilliams.is_at_usenet.com>
Date: Wed, 07 Nov 2001 16:34:57 -0500
Message-ID: <p1ajut8orckoa3h8aga7jpnc03q1t4ub07@4ax.com>

>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?

Not correct. Only COMP, CLOSE, CAN are values that take precedence.

>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)

Which might be translated to..?

select monthname(changedate), count(1)
from workstat w
where wostat = 'INPRG'

	and changedate <= '3/1/2000' 
	and not exists 
	(select 1 from workstat 
		where wonum = w.wonum 
		and wostat in ('CLOSE',CAN','COMP')
-- or wostat like 'C%'
		and changedate <= '3/1/2000') 

group by monthname(changedate)

-Remo Received on Wed Nov 07 2001 - 15:34:57 CST