sql server - Use SQL to count cases in a certain state at a certain time -

I need to develop a query that will calculate the number of total open cases per month. I have a 'cases' table with an ID and a name, and a 'State_change' table containing a data column, a cassid column and a state.

How can I calculate the number of cases in each month that there is a record with 'open' in the state, but with the closure of the state without any such record?

I am using SQL Server 2000.

You should close it (T-SQL):

  SEND MONTH (s.casedate) M, YEAR (S.Cezet) y, COUNT (CICIDIID CICID), Cncd = C on the status_places. CCD where SRT = 'Open' / * "State with 'Open' * / and C. & LT; GateDet () / *" in the past "* / and not (/ *" Record with state closed Without the '' off '' * * / selection 1 from the states where i.caseid = s.caseid and i.state = 'closed'), the group has monthly (squset), YEAR (s.casedate)  

Edit: A small accessory table is required to make data on all twelve months (independent of the actual cases in these months) (Let's call it month adds), in which a column with numbers from 1 to 12 but does not do anything (let's also month ). Against this:

  SELECT m.month, COUNT (DISTINCT c.caseid) cases c count_cases Join C INNER state_changes s s.caseid = c.caseid LEFT JOIN month on M. Mm = month (s. Czet) where STT = 'Open' and year (c.c.) = year (gatetet)) * * which is not * / and (1 selection from state) I_secondid = i.caseid = s.caseid and i.state = 'closed') Group by M. Month order mmm