Closed question roomba

3

Please login or register to vote for this query.

(click on this box to dismiss)

With great power comes great responsibility. See http://stackoverflow.com/help/roomba. The check for reopen votes is usually too conservative, but votes should expire after 14 days at the most. This query excludes questions that are duplicate targets, even though the roomba would be willing to delete them.

Woodworking

Q&A for professional and amateur woodworkers

--MaxQScore: Max Question Score
declare @qscore INT = ##MaxQScore:INT?1##
--MaxAScore: Max Answer Score
declare @ascore INT = ##MaxAScore:INT?1##

select distinct top ##Top:int?150##
     p.Id as [Post Link],
     (CASE WHEN p.OwnerUserId IS NOT NULL THEN
     'site://users/' + CAST(p.OwnerUserId AS nvarchar) + '|' ELSE '' END) +
     COALESCE(p.OwnerDisplayName, u.DisplayName) AS [User Link],
     p.Score,
     p.CreationDate,
     p.ViewCount,
     crt.Name as [Close Type]
from posts p
left join Users u on u.Id = p.OwnerUserId
left join PostHistory ph on ph.postid = p.id
left join CloseReasonTypes crt on ph.Comment = crt.Id
left join Votes v on (v.PostId = p.Id and v.VoteTypeId = '10')
inner join PostHistoryTypes phn on phn.Id = ph.PostHistoryTypeId
inner join Posts a on p.Id = a.ParentId
where 
ph.comment != 1 and ph.comment != 101
and ph.posthistorytypeid = 10
and p.acceptedanswerid is null
and p.closeddate is not null
and p.tags like '%' + ##TagsContain:string?%%## + '%'
and p.tags not like '%' + ##TagsDoNotContain:string?xyzzy## + '%'
--and p.viewcount < ##ViewCount:int?20000##
and datediff(d, p.closeddate, getdate()) >= 9
--and p.score >= ##MinScore:int?-10##
and p.score <= @qscore
group by p.id, p.OwnerUserId, p.OwnerDisplayName, u.DisplayName, p.score, p.CreationDate, p.viewcount, v.votetypeid, ph.comment,
         crt.Name
having count(v.votetypeid) = 0 and max(a.score) <= @ascore
order by p.score asc, p.viewcount asc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results