Number of open, closed and deleted questions by month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

SET SHOWPLAN_ALL ON

GO

;WITH

q AS (
  SELECT
    Id,
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      1) AS Month,
    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),
  
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
cross join p AS pClosed 
cross join p AS pOpen 
WHERE 
   pclosed.month = pDeleted.month
  and  pOpen.month = pDeleted.Month
  AND pDeleted.DeletionStatus = 'deleted'
  AND pClosed.DeletionStatus = 'closed'
  AND pOpen.DeletionStatus = 'open'
ORDER BY Month DESC

go 
SET SHOWPLAN_ALL OFF;  
GO

Enter Parameters

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