5

Please login or register to vote for this query.

(click on this box to dismiss)

The number of questions posted in each month, and the number of closed questions among them

Q&A for Drupal developers and administrators

-- Closed questions per month
-- The number of questions posted in each month, and the number of closed questions among them

CREATE TABLE #NQuestions (Year int NOT NULL, Month int NOT NULL, Questions int NOT NULL);
CREATE TABLE #NClosed (Year int NOT NULL, Month int NOT NULL, Closed int NOT NULL);

GO

CREATE UNIQUE INDEX #IX_NQuestions ON #NQuestions(Year, Month);
CREATE UNIQUE INDEX #IX_NClosed on #NClosed(Year, Month);

GO

INSERT INTO #NQuestions (Year, Month, Questions)
SELECT 9999, 99, COUNT(*)
FROM Posts p
WHERE p.PostTypeId = 1
UNION ALL
SELECT YEAR(p.CreationDate), MONTH(p.CreationDate), COUNT(*)
FROM Posts p
WHERE p.PostTypeId = 1
GROUP BY YEAR(p.CreationDate), MONTH(p.CreationDate);

INSERT INTO #NClosed(Year, Month, Closed)
SELECT 9999, 99, COUNT(*)
FROM Posts p
WHERE p.PostTypeId = 1 AND ClosedDate IS NOT NULL
UNION ALL
SELECT YEAR(p.CreationDate), MONTH(p.CreationDate), COUNT(*)
FROM Posts p
WHERE p.PostTypeId = 1 AND ClosedDate IS NOT NULL
GROUP BY YEAR(p.CreationDate), MONTH(p.CreationDate);

GO

SELECT
  CASE
    WHEN nq.Year = 9999 THEN 'Total'
    ELSE LEFT(DATENAME(month, STR(nq.Year, 4) + '-' + REPLACE(STR(nq.Month, 2), ' ', '0') + '-01'), 3) + ' ' + STR(nq.Year, 4)
  END AS Month,
  nq.Questions, ISNULL(nc.Closed, 0) AS Closed,
  CAST(ISNULL(nc.Closed, 0) AS float) / nq.Questions AS Ratio
FROM
  #NQuestions nq
  LEFT JOIN #NClosed nc ON nq.Year = nc.Year AND nq.Month = nc.Month
ORDER BY nq.Year DESC, nq.Month DESC;

fork query

created oct 1 11 Tsuyoshi Ito