TBRC

0

Please login or register to vote for this query.

(click on this box to dismiss)

Italian Language Meta

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

--MaxQScore: Max Question Score
declare @qscore int = 1
--MaxAScore: Max Answer Score
declare @ascore int = 1
declare @now datetime = getdate()

select distinct
  q.Id as [Post Link],
  q.Score,
  max(a.Score) as [Best Answer],
  q.CreationDate,
  q.ClosedDate,
  q.ViewCount
from Posts q
-- find edits from the past 9 days
left join PostHistory ph on ph.PostId = q.Id
  and datediff(d, ph.CreationDate, @now) <= 9
-- find reopen votes cast in the last 14 days
left join Votes v on v.PostId = q.Id and v.VoteTypeId = '7'
  and datediff(d, v.CreationDate, @now) <= 14
left join Posts a on q.Id = a.ParentId
-- see if question is a duplicate target
left join PostLinks pl on RelatedPostId = q.Id and LinkTypeId = 3
where
  -- question was closed more than 9 days ago
  q.ClosedDate is not null
  and datediff(d, q.ClosedDate, @now) > 9
  -- not closed as a duplicate
  and (select top 1 Comment
   from PostHistory ph
   where ph.PostId = q.Id and PostHistoryTypeId = 10
   order by Id desc) not in (1, 101)
  and q.Score <= @qscore
  -- is not locked
  and coalesce((select top 1 PostHistoryTypeId
    from PostHistory
    where PostHistory.PostId = q.Id
      and PostHistory.PostHistoryTypeId in (14, 15) -- post locked, unlocked
    order by Id desc), 0) <> 14
  and q.AcceptedAnswerId is null
  and (q.Score > 0 or a.Score > 0)
group by q.Id, q.Score, q.CreationDate, q.ClosedDate, q.ViewCount
having coalesce(max(a.Score),0) <= @ascore
  and count(ph.Id) = 0 -- no recent edits
  and count(v.Id) = 0 -- no recent reopen votes
  and count(pl.Id) = 0 -- not a duplicate target
  order by max(a.Score) desc, q.Score, CreationDate

Enter Parameters

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