Distribution of Close Reasons

0

Please login or register to vote for this query.

(click on this box to dismiss)

Summary of the number of questions closed by the close reason type.

The Workplace

Q&A for members of the workforce navigating the professional setting

-- Month by month total of questions closed / re-opened by close reason.

DECLARE @closeReasons table
(
    Id int NOT NULL PRIMARY KEY,
    Name nvarchar(200) NOT NULL
)

INSERT INTO @closeReasons
    VALUES
    (1, 'Exact duplicate'),
    (2, 'Off topic'),
    (3, 'Subjective and argumentative'),
    (4, 'Not a real question'),
    (7, 'Too localized'),
    (20, 'Noise or pointless'),
    (101, 'Duplicate'),
    (102, 'Off topic'),
    (103, 'Unclear'),
    (104, 'Too Broad'),
    (105, 'Opinion Based')

SELECT
    CONVERT(nvarchar(6), p.CreationDate, 112) AS Period,
    (CASE WHEN cr.Name IS NULL THEN '(Unknown)' ELSE cr.Name END) AS 'Close Reason',
    SUM(1) AS CloseCount,
    COUNT(pho.Id) AS ReopenCount
    FROM PostHistory phc
    LEFT OUTER JOIN @closeReasons cr ON cr.Id = CAST(phc.Comment AS int)
    LEFT OUTER JOIN Posts p ON p.Id = phc.PostId
    LEFT OUTER JOIN PostHistory pho on pho.PostId = p.Id AND pho.PostHistoryTypeId = 11
    WHERE 
      phc.PostId = p.Id AND phc.PostHistoryTypeId = 10
        AND p.CreationDate >= '2013-01-01'
    GROUP BY CONVERT(nvarchar(6), p.CreationDate, 112), cr.Name
    ORDER BY CONVERT(nvarchar(6), p.CreationDate, 112), cr.Name

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results