Closed questions per month

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

Ask Ubuntu Meta

Q&A about the site for Ubuntu users and developers

-- 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;

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results