Longest times between close-reopen

1

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Different

Q&A for power users of Apple hardware and software

WITH Raw AS (
  SELECT
    PostId,
    PostHistoryTypeId,
    RANK() OVER (PARTITION BY PostId ORDER BY Id) AS No,
    CreationDate
  FROM PostHistory
  WHERE
    PostHistoryTypeId BETWEEN 10 AND 11
    AND
    PostId IN (
      SELECT PostId
      FROM PostHistory
      WHERE PostHistoryTypeId = 11
    )
)
SELECT TOP 100
  C.PostId AS [Post Link],
  C.CreationDate AS Closed,
  R.CreationDate AS Reopened
FROM Raw C, Raw R, Posts
WHERE
  C.PostId = R.PostId
  AND
  C.PostHistoryTypeId = 10
  AND
  R.PostHistoryTypeId = 11
  AND
  C.No + 1 = R.No
  AND
  C.PostId = Posts.Id
  AND
  Posts.ClosedDate IS NULL
ORDER BY DATEDIFF(s, C.CreationDate, R.CreationDate) DESC

Enter Parameters

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