0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for community managers, administrators, and moderators

declare @dateFilter date
set @dateFilter = DATEADD(DAY, -##IgnoreDays?7##, GETDATE())

SELECT
  p.Id as [Post Link],
  SUM(p.Score) as Score,
  SUM(p.CommentCount) as CommentCount,
  MAX(p.LastActivityDate) as LastActivityDate,
  pht.Name
  --p.AcceptedAnswerId
  --pt.Name
FROM 
  Posts p
  JOIN PostTypes pt ON pt.Id = p.PostTypeId
  JOIN PostHistory ph ON ph.PostId = p.Id
  JOIN PostHistoryTypes pht ON pht.Id = ph.PostHistoryTypeId
WHERE 
  p.Score >= 0
  AND p.ClosedDate IS NULL
  AND (p.AnswerCount IS NULL OR p.AnswerCount = 0)
  AND p.CreationDate <= @dateFilter
  AND p.CommunityOwnedDate IS NULL
  AND pt.Name = 'Question'
GROUP BY
  p.Id,
  pht.Name
HAVING
MAX(CASE WHEN ((pht.Name = 'Post Locked')) THEN 1 ELSE 0 END) = 0
ORDER BY
  Score DESC,
  LastActivityDate ASC

view current version

edited apr 15 14 MrHen

created jan 16 14 anonymous