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_at_4ax.com>
group by monthname(changedate)
Date: Wed, 07 Nov 2001 16:34:57 -0500
Message-ID: <p1ajut8orckoa3h8aga7jpnc03q1t4ub07_at_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 - 22:34:57 CET