0

Please login or register to vote for this query.

(click on this box to dismiss)

Questions that have gone through a close/reopen cycle at least once.

Q&A for SharePoint enthusiasts

-- Community Controversy
-- Questions that have gone through a close/reopen cycle at least once.

SELECT
    PostId AS [Post Link],
    CloseCount,
    ReopenCount,
    CurrentlyClosed
    FROM
    (
        SELECT
            Id AS PostId,
            (CASE WHEN ClosedDate IS NULL THEN 0 ELSE 1 END) AS CurrentlyClosed,
            (SELECT COUNT(*) FROM PostHistory closed WHERE closed.PostId = p.Id AND closed.PostHistoryTypeId = 10) AS CloseCount,
            (SELECT COUNT(*) FROM PostHistory opened WHERE opened.PostId = p.Id AND opened.PostHistoryTypeId = 11) AS ReopenCount
            FROM Posts p
            WHERE p.PostTypeId = 1
    ) a
    WHERE (CloseCount + ReopenCount) > 1
    ORDER BY
        (CloseCount + ReopenCount) DESC,
        CloseCount DESC,
        ReopenCount DESC

fork query

created dec 29 10 Jon Seigel