使用SQL在特定时间对处于特定状态的案例进行计数

这将使您关闭(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)

编辑:要对所有12个月进行统计(独立于这些月的实际案例),您需要一个小的帮助表(我们称之为 month ,只包含一列(我们称之为 月 以及)从1到12的数字。然后你加入反对:

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

为打开的事件和关闭的事件创建状态更改表的查询。

创建一个查询,该查询对案例ID执行打开到关闭的外部联接,并从两个查询返回案例ID

查询后一个查询结果中“close”事件查询的ID为空的行

计算后一个查询结果中的行数。

非常粗略的东西(从我的头顶上,没有修正):

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