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

In article <u5bgut0lv9dtvp24snregrv49t9e589v6r_at_4ax.com>, Remo Williams says...
>
>Given a table
>workstat
>-------------
>wonum (integer),
>wostat (varchar(6)),
>changedate (datetime)
>
>And the following data:
>
>wonum wostatus changedate
>--------------------------------------------------
>100 WSCH 2000-01-01
>200 WSCH 2000-01-01
>300 WSCH 2000-01-01
>400 WSCH 2000-01-01
>500 WSCH 2000-01-01
>100 INPRG 2000-01-15
>200 INPRG 2000-01-15
>300 INPRG 2000-02-10
>400 INPRG 2000-03-10
>500 INPRG 2000-03-18
>100 COMP 2000-04-01
>200 COMP 2000-04-01
>300 COMP 2000-04-01
>400 COMP 2000-04-01
>500 COMP 2000-04-01
>100 CLOSE 2000-05-01
>
>I want to write an SQL statement that will give me a running count of
>rows that are INPRG based on month. For January, two are INPRG; for
>February, three are INPRG: the two previous ones and the current one;
>for March, five are INPRG, for April, none are INPRG.
>
>I can find out which rows are currently INPRG for each month using
>'GROUP BY', but I can't seem to manage a running total.
>
>Clues?
>
>-Remo
>
>

If your db can handle grouping sets or something similar, you could try:

select coalesce(monthname(changedate),'Total'), count(1) from workstat where wostat = 'INPRG' group by grouping sets (monthnam e(changedate),())

if not you can try something like:

select monthname(changedate), count(1) from workstat where wostat = 'INPRG' group by monthname(changedate)
union
select 'Total', sum(1) from workstat where wostat = 'INPRG'

hope it helps
/Lennart Received on Tue Nov 06 2001 - 21:43:08 CET