1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

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

fork query

edited nov 4 Blindspots

created oct 30 Martin20528