Reopened posts and their edits

0

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

SELECT
Posts.Id AS [Post Link],
Posts.Score,
Posts.ViewCount AS views,
'site://posts/'+CAST(Posts.ID AS varchar(10))+'/revisions|'+CAST(COUNT(*) AS varchar(3)) AS 'Edits',
Posts.CreationDate AS 'Post created',
closures.CreationDate AS 'Post closed',
reopens.CreationDate AS 'Post reopened'
FROM PostHistory reopens
JOIN Posts ON Posts.Id = reopens.PostId
JOIN PostHistory closures ON reopens.PostId = closures.PostId
JOIN PostHistory edits ON reopens.PostId = edits.PostId
WHERE reopens.PostHistoryTypeId = 11 -- Post reopened
  AND closures.PostHistoryTypeID = 10 -- Post closed
  AND edits.PostHistoryTypeID = 5 -- Body edits
  AND closures.CreationDate < edits.CreationDate
  AND edits.CreationDate < reopens.CreationDate
GROUP BY Posts.Id, Posts.Score, Posts.ViewCount, Posts.CreationDate, closures.CreationDate, reopens.CreationDate
ORDER BY reopens.CreationDate DESC

Enter Parameters

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