All questions status and close votes

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.

Operations Research Meta

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 AND rtr.ReviewTaskResultTypeId <> 1 WHERE rt.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(DISTINCT(rt.Id))+COUNT(DISTINCT(rtr.Id)) FROM ReviewTasks AS rt LEFT JOIN ReviewTaskResults AS rtr ON rtr.ReviewTaskId = rt.Id AND rtr.ReviewTaskResultTypeId = 6 WHERE rt.PostId = p.Id AND rt.ReviewTaskTypeId = 2 AND rt.CreationDate > ISNULL(IIF(p.DeletionDate IS NOT NULL OR p.ClosedDate IS NOT NULL,NULL,(SELECT MAX(CreationDate) FROM ReviewTasks WHERE PostId = p.Id AND ( ( ReviewTaskTypeId = 2 AND ReviewTaskStateId = 2 ) OR ReviewTaskTypeId = 6 ) )),'2000-01-01')) AS [Close Votes]
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;

Enter Parameters

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