1

Please login or register to vote for this query.

(click on this box to dismiss)

List all questions, along with score, descriptive status, and close votes. - For closed questions, the close votes count includes all votes, except for mod hammers. - For open questions, the close votes count includes pending and aged away votes only.

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

SELECT rt.*, rtr.* FROM ReviewTasks AS rt LEFT JOIN ReviewTaskResults AS rtr ON rtr.ReviewTaskId = rt.Id WHERE rt.PostId = ##PostId:int?0##;
SELECT v.* FROM Votes AS v WHERE v.PostId = ##PostId:int?0##

SELECT TOP ##MostRecent:int?100##
  p.Id AS [Post Link],
  p.Score AS [Score],
  IIF(p.DeletionDate IS NOT NULL and p.ClosedDate IS NOT NULL,
                                 'Deleted after Closure',
  IIF(p.DeletionDate IS NOT NULL,'Deleted by User',
  IIF(p.ClosedDate IS NOT NULL,  'Closed as '+crt.Name,
  IIF(ISNULL(p.AnswerCount,0)=0, 'Unanswered',
  IIF(p.AcceptedAnswerId IS NULL,STR(p.AnswerCount)+' Answers',
                                 'Accepted Answer'))))) [Status],
  p.CreationDate AS [Created At],
  (SELECT COUNT(rt.Id) FROM ReviewTasks AS rt WHERE p.Id = rt.PostId and rt.ReviewTaskTypeId = 2) AS [RT1],
  (SELECT COUNT(rtr.Id) FROM ReviewTasks AS rt INNER JOIN ReviewTaskResults AS rtr ON rt.Id = rtr.ReviewTaskId WHERE p.Id = rt.PostId) AS [RT2],
  (SELECT COUNT(rtr.Id) FROM ReviewTasks AS rt INNER JOIN ReviewTaskResults AS rtr ON rt.Id = rtr.ReviewTaskId WHERE p.Id = rt.PostId AND rtr.ReviewTaskResultTypeId IN (6,8,12)) AS [RT]
FROM PostsWithDeleted AS p
LEFT JOIN (SELECT * FROM PostHistory WHERE PostHistoryTypeId = 10) AS ph ON p.Id = ph.PostId LEFT JOIN (SELECT * FROM PostHistory WHERE PostHistoryTypeId = 10) AS pht ON p.Id = pht.PostId AND ( ph.CreationDate < pht.CreationDate OR ( ph.CreationDate = pht.CreationDate AND ph.Id < pht.Id ) )
LEFT JOIN CloseReasonTypes crt ON CAST(ph.Comment AS INT) = crt.Id
WHERE
  p.PostTypeId = 1 AND
  pht.Id IS NULL
ORDER BY p.CreationDate DESC;

view current version

edited dec 22 Arnon.Weinberg

created dec 6 17 ArtOfCode-