0

Please login or register to vote for this query.

(click on this box to dismiss)

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 pOpen 
cross join p AS pClosed 
cross join p AS pDeleted
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

fork query

edited may 23 17 rene

created mar 27 17 peterh