3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for astronomers and astrophysicists

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 IN (1,2)),
  
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

view current version

edited mar 27 17

created mar 27 17 peterh