0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for operations research and analytics professionals, educators, and students

WITH ClosedQuestionIds AS (
  SELECT DISTINCT PostId, CreationDate AS CloseDate, Comment AS CloseReasonId
  FROM PostHistory
  WHERE PostHistoryTypeId=10 --- closure
  AND YEAR(CreationDate)=##ClosedYear:int##
  AND Comment != 101 --- not "duplicate"
),

PostTitleBeforeClose AS (
SELECT TitleHistory.*
  FROM ClosedQuestionIds q
  OUTER APPLY (
                SELECT TOP 1 * --- select first
                FROM PostHistory h
                WHERE h.PostId = q.PostId
                AND h.CreationDate < q.CloseDate --- history before closure
                AND h.PostHistoryTypeId IN (1, 4) --- initial title or edit
                ORDER BY h.CreationDate DESC --- order by history date (newest first)
              ) AS TitleHistory
),

PostBodyBeforeClose AS (
SELECT BodyHistory.*
  FROM ClosedQuestionIds q
  OUTER APPLY (
                SELECT TOP 1 * --- select first
                FROM PostHistory h
                WHERE h.PostId = q.PostId
                AND h.CreationDate < q.CloseDate --- history before closure
                AND h.PostHistoryTypeId IN (2, 5) --- initial body or edit
                ORDER BY h.CreationDate DESC --- order by history date (newest first)
              ) AS BodyHistory
)

SELECT q.*, cr.Name as CloseReason, pt.Text as Title, pb.Text as Body
FROM ClosedQuestionIds q
JOIN PostTitleBeforeClose AS pt on pt.PostId=q.PostId
JOIN PostBodyBeforeClose AS pb on pb.PostId=q.PostId
JOIN CloseReasonTypes AS cr on cr.Id = q.CloseReasonId

fork query

edited aug 30 anonymous

created apr 25 princessmononoke