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

From: Remo Williams <remowilliams.is_at_usenet.com>
Date: Tue, 06 Nov 2001 13:38:07 -0500
Message-ID: <u5bgut0lv9dtvp24snregrv49t9e589v6r@4ax.com>

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 Received on Tue Nov 06 2001 - 12:38:07 CST