Use SQL to count cases in a certain state at a certain time

Question

Hi

I need to develop a query that will count the total number of 'open' cases per month. I have a 'cases' table with an id and a name, and a 'state_changes' table with a datetime column, a caseid column and a state.

How can I calculate the number of cases in each month that have a record with state 'open' in the past, but without a corresponding record with state closed?

I'm using SQL server 2000.

Answer 1

This should get you close (T-SQL):

SELECT
  MONTH(s.casedate) m,
  YEAR(s.casedate)  y,
  COUNT(DISTINCT c.caseid)   count_cases
FROM
  cases c
  INNER JOIN state_changes s ON s.caseid = c.caseid
WHERE
  s.state = 'open'           /* "with state 'open'" */
  AND s.casedate < GETDATE() /* "in the past" */
  AND NOT EXISTS (           /* "without corresp. record with state 'closed'" */
    SELECT 1 FROM state_changes i WHERE i.caseid = s.caseid AND i.state = 'closed'
  )
GROUP BY
  MONTH(s.casedate),
  YEAR(s.casedate)

EDIT: To make a statistic over all twelve months (independent of actual cases existing in these months) you need a small helper table (let's call it month), that contains nothing but one column (let's call that month as well) with numbers from 1 to 12. Then you join against it:

SELECT
  m.month,
  COUNT(DISTINCT c.caseid) count_cases
FROM
  cases c
  INNER JOIN state_changes s ON s.caseid = c.caseid
  LEFT  JOIN month         m ON m.month  = MONTH(s.casedate)
WHERE
  s.state = 'open'
  AND YEAR(c.createddate) = YEAR(GETDATE())  /* whatever */
  AND NOT EXISTS (
    SELECT 1 FROM state_changes i WHERE i.caseid = s.caseid AND i.state = 'closed'
  )
GROUP BY
  m.month
ORDER BY
  m.month

Answer 2

Create a query of the state changes tables for open events and one for close events.

Create a query that does an outer join of the open to the closed on the case ID returning the case ID from both queries

Query the latter query result for rows where the ID from the "close" event query is null

Count the number of rows in the latter query result.

Something very roughly like (off the top of my head, without correction):

SELECT COUNT (T1.CaseID) FROM (SELECT T1.CaseID AS T1_CaseID, T2.CaseID AS T2_CaseID 
FROM ((SELECT CaseID FROM state_changes WHERE state = 'open' AND timestamp BETWEEN 1-Jan-09 AND 30-Jan-09) AS T1 OUTER JOIN (SELECT CaseID FROM state_changes WHERE state = 'closed' AND timestamp BETWEEN 1-Jan-09 AND 30-Jan-09) AS T2 ON T1.CaseID = T2.CaseID)) WHERE T2_CaseID = NULL