Linked questions in [YEAR]

0

Please login or register to vote for this query.

(click on this box to dismiss)

Freelancing Meta

Q&A about the site for self-employed and freelance workers

WITH ClosedQuestionIds AS (
  SELECT DISTINCT PostId, CreationDate AS CloseDate, Comment AS CloseReasonId, Text
  FROM PostHistory
  WHERE PostHistoryTypeId=10 --- closure
  AND YEAR(CreationDate)=##ClosedYear:int##
  AND Comment = 101 --- is "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 TOP 10 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

Enter Parameters

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