1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people seeking specific software recommendations

-- If the question is more than 365 days old, and ...
select Posts.CreationDate, Posts.OwnerUserId, Users.DisplayName, ViewCount, 
  Posts.Id as [Post Link], Tags
from Posts, Users, PostHistory

where 
  -- joining tables
  Users.Id = Posts.OwnerUserId 
  and PostHistory.Id = Posts.Id
  
  -- satisfying conditions
  and Posts.PostTypeId = 1 -- is a question
  and Posts.Score <= 0 -- has a score of 0
  and Posts.AnswerCount = 0 -- has no answers
  -- and PostHistory.PostHistoryTypeId <> 14  -- is not locked
  and Posts.CommentCount < 2  -- has 1 or 0 comments
  and Posts.ViewCount <= DATEDIFF(DAY, Posts.CreationDate, GETDATE()) * 1.5 -- has view count <= the age of the question in days times 1.5
  and DATEDIFF(DAY, Posts.CreationDate, GETDATE()) > (365 - ##WillBedeletedInXDays?10##) -- is old
order by Posts.CreationDate asc


/*
Documentation.

PostHistory.PostHistoryTypeId:
   10 Post Closed 
   11 Post Reopened
   12 Post Deleted
   13 Post Undeleted
   14 Post Locked
   15 Post Unlocked
   33 Post Notice Added
   34 Post Notice Removed
   
 */

view current version

edited mar 29 15

created mar 29 15 Franck Dernoncourt