Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation
--WITH q AS ( SELECT DATEFROMPARTS( DATEPART(yyyy, CreationDate), DATEPART(mm, CreationDate), 1) AS Month, COUNT(*) AS AllQuestions, COUNT(DeletionDate) AS Deleted/* CASE WHEN DeletionDate IS NOT NULL THEN 'deleted' WHEN DeletionDate IS NULL AND ClosedDate IS NOT NULL THEN 'closed' ELSE 'open' END AS DeletionStatus*/ FROM PostsWithDeleted WHERE PostTypeId=1 GROUP BY DATEFROMPARTS( DATEPART(yyyy, CreationDate), DATEPART(mm, CreationDate), 1) /* ), p AS ( SELECT Month, DeletionStatus, COUNT(*) AS N FROM q GROUP BY Month, DeletionStatus ) SELECT pDeleted.Month, pDeleted.N + pClosed.N + pOpen.N AS 'All', pDeleted.N + pClosed.N AS Closed, pDeleted.N AS 'Deleted' FROM p AS pDeleted, p AS pClosed, p AS pOpen WHERE pDeleted.Month = pOpen.Month AND pDeleted.Month = pClosed.Month AND pDeleted.DeletionStatus = 'deleted' AND pClosed.DeletionStatus = 'closed' AND pOpen.DeletionStatus = 'open' ORDER BY Month DESC*/