The number of questions posted in each month, and the number of closed questions among them
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;