Q&A about the site for administrators and users of the CiviCRM Constituent Relationship Management software
SELECT TOP ( CASE WHEN ##numberOfHits?0## = 0 THEN 50000 ELSE ##numberOfHits## END ) rt.PostId AS [Post Link], rt.PostId, p.Title, h.UserId, u.DisplayName, 'site://review/' + CASE rt.ReviewTaskTypeId WHEN 1 THEN 'suggested-edits' WHEN 2 THEN 'close' WHEN 3 THEN 'low-quality-posts' WHEN 4 THEN 'first-posts' WHEN 5 THEN 'late-answers' WHEN 6 THEN 'reopen' WHEN 12 THEN 'first-questions' WHEN 13 THEN 'first-answers' ELSE 'unknown-type' END + '/' + CAST(rt.Id AS NVARCHAR) + '|Review task' AS [Review Task Link], rt.Id AS [ReviewTaskId], rt.CreationDate AS [ReviewTask CD], p.CreationDate AS [Post CD], h.CreationDate AS [History CD], h.PostHistoryTypeId AS [PostHistoryTypeId], -- rt.DeletionDate, rt.ReviewTaskTypeId, CASE rt.ReviewTaskStateId WHEN 1 THEN 'Active' WHEN 2 THEN 'Completed' WHEN 3 THEN 'Invalidated' ELSE 'unknown-state' END AS [Review Task State], CASE rt.ReviewTaskTypeId WHEN 1 THEN 'Suggested Edit' WHEN 2 THEN 'Close Votes' WHEN 3 THEN 'Low Quality Posts' WHEN 4 THEN 'First Post' WHEN 5 THEN 'Late Answer' WHEN 6 THEN 'Reopen Vote' WHEN 12 THEN 'First Questions' WHEN 13 THEN 'First Answers' ELSE 'unknown-type' END AS [Task Type], CASE rtr.ReviewTaskResultTypeId WHEN 1 THEN 'Skip' WHEN 2 THEN 'Approve (suggested edits)' WHEN 3 THEN 'Reject (suggested edits)' WHEN 4 THEN 'Delete (low quality)' WHEN 5 THEN 'Edit (first posts, late answers, low quality)' WHEN 6 THEN 'Close (close, low quality)' WHEN 7 THEN 'Looks OK (low quality)' WHEN 8 THEN 'Do Not Close (close)' WHEN 9 THEN 'Recommend Deletion (low quality answer)' WHEN 10 THEN 'Recommend Close (low quality question)' WHEN 11 THEN 'Other Action (first posts)' WHEN 12 THEN 'Reopen (reopen)' WHEN 13 THEN 'Leave Closed (reopen)' WHEN 14 THEN 'Edit and Reopen (reopen)' WHEN 15 THEN 'Excellent (community evaluation)' WHEN 16 THEN 'Satisfactory (community evaluation)' WHEN 17 THEN 'Needs Improvement (community evaluation)' WHEN 18 THEN 'No Action Needed (first posts, late answers)' ELSE 'unknown-result' END AS [Task Result], rtr.ReviewTaskResultTypeId, -- pwd.ClosedDate, p.DeletionDate -- pwd.DeletionDate FROM ReviewTasks rt RIGHT OUTER JOIN ReviewTaskResults rtr ON rtr.Id = rt.CompletedByReviewTaskId -- INNER JOIN PostsWithDeleted pwd ON pwd.Id = rt.PostId INNER JOIN Posts p ON p.Id = rt.PostId INNER JOIN PostHistory h ON h.PostId = rt.PostId INNER JOIN Users u ON u.Id = h.UserId WHERE rt.ReviewTaskTypeId IN (##types?4,12##) AND (rt.CreationDate >= '##startDate?2005-01-01##') AND (rt.CreationDate <= '##endDate?2030-12-31##') ORDER BY rt.CreationDate DESC