0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for administrators, end users, developers and designers for Craft CMS

SELECT
  CONCAT('site://review/late-answers/', reviewTask.Id, '|Review') AS [Review Link]
  ,CONCAT('site://posts/', reviewTask.PostId, '/timeline|Post ', reviewTask.PostId, '''s Timeline') AS [Post Timeline]
  ,COUNT(reviewAction.Id) AS [Total Actions]
  ,completionType.Name AS [Final Review Action]
  ,reviewTask.CreationDate AS [Review Created]
FROM ReviewTasks reviewTask
JOIN ReviewTaskResults reviewAction ON reviewTask.Id = reviewAction.ReviewTaskId
JOIN ReviewTaskResults finalReviewAction ON reviewTask.CompletedByReviewTaskId = finalReviewAction.Id
JOIN ReviewTaskResultTypes completionType ON finalReviewAction.ReviewTaskResultTypeId = completionType.Id
WHERE 1=1
  AND reviewTask.ReviewTaskTypeId = 5 -- Late Answers Queue only
  AND reviewTask.ReviewTaskStateId = 2 -- Completed reviews only
  AND reviewAction.ReviewTaskResultTypeId <> 1 -- Exclude Skip actions
  AND finalReviewAction.ReviewTaskResultTypeId IN (4, 9) -- Only include reviews which resolved with delete
GROUP BY reviewTask.Id, reviewTask.PostId, reviewTask.CreationDate, completionType.Name
ORDER BY COUNT(reviewAction.Id) DESC, reviewTask.CreationDate

fork query

edited nov 10 anonymous

created jul 6 22 zcoop98