Questions in danger of auto-delete after 9 days since closure - with answers


Please login or register to vote for this query.

(click on this box to dismiss)

Ask Different

Q&A for power users of Apple hardware and software

---This includes simply the closed questions with score<=0, 
---no positively scored answers
---and no accepted answer
with maxscore_cte AS
  q.Id AS qid,
  MAX(a.Score) AS MaxAScore
FROM Posts q INNER JOIN Posts a ON a.ParentId=q.Id

SELECT TOP(##num?500##)
  q.Id AS [Post Link], 
  q.OwnerUserId AS [User Link],
  q.ClosedDate, DATEDIFF(day, q.ClosedDate, CAST(GETDATE() AS Date)) AS [Days since closure],
  q.LastEditDate, DATEDIFF(day, q.LastEditDate, CAST(GETDATE() AS Date)) AS [Days since last edit],
  q.CreationDate, DATEDIFF(day, q.CreationDate, CAST(GETDATE() AS Date)) AS [Days],
  q.AnswerCount, m.MaxAScore, q.CommentCount,
  q.Score, q.ViewCount, q.Tags
FROM Posts q LEFT OUTER JOIN maxscore_cte m ON q.Id=m.qid
WHERE (q.Score<=0) AND (q.ClosedDate IS NOT NULL) AND (q.AcceptedAnswerId IS NULL) AND (m.MaxAScore<=0) AND (q.PostTypeId=1) AND
---do not include locked questions
  (q.Id not IN 
    (SELECT ph1.PostId FROM PostHistory ph1 INNER JOIN Posts q ON ph1.PostId=q.Id WHERE (q.PostTypeId=1) AND (ph1.PostHistoryTypeId=14) AND 
      (q.Id NOT IN 
        (SELECT ph2.PostId FROM PostHistory ph2 WHERE ph2.PostHistoryTypeId=15)
  ) AND
---do not include queestions closed as duplicates
  (q.Id NOT IN
    (SELECT p.Id FROM Posts p INNER JOIN PostLinks pl ON pl.PostId=p.Id
      WHERE (p.PostTypeId = 1)
        AND (p.ClosedDate IS NOT NULL) 
        AND (pl.LinkTypeId=3)
ORDER BY q.ClosedDate ASC

Enter Parameters

Switch to meta site
loading Hold tight while we fetch your results