Closed, scoring <= 0, no accepted answers or answers scoring > 0, no reopen votes, no recent edits

0

Please login or register to vote for this query.

(click on this box to dismiss)

Sound Design

Q&A for sound engineers, producers, editors, and enthusiasts

-- not really fair to use getdate() here if the data dump is a few days out of date, so go with the most recent post
declare @today as datetime = (select max(creationdate) from Posts)

select top 4000 q.Id as [Post Link]

from Posts q

join PostHistory h on h.PostId = q.Id

-- closed over deadline days ago
where ClosedDate < @today-9

-- not duplicate
and PostHistoryTypeId=10
and Comment<>'1'

-- score can't be above 0
and Score <= 0

-- not locked
and isnull(
(select top 1 PostHistoryTypeId from PostHistory
where PostId=q.Id and PostHistoryTypeId in (14,15,35) 
order by CreationDate desc)
,0) not in (14,35)

-- no answers scoring above 0 either
and not exists (select 1 from Posts a where ParentId=q.Id and Score>=1)
-- no accepted answers
and AcceptedAnswerId is null

-- pending reopen votes
and not exists (select 1 from Votes where VoteTypeId=7 and PostId=q.Id)

-- no edits in the past deadline days
and not exists (select 1 from PostHistory where PostHistoryTypeId in (4,5) and PostId=q.Id and CreationDate > @today-##deadline##)

order by q.CreationDate desc

Enter Parameters

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