0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

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

SELECT
  p.Id as [Post Link],
  MAX(p.Score) as Score,
  MAX(p.CommentCount) as CommentCount,
  MAX(p.LastActivityDate) as LastActivityDate
  --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 = 11
  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
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 4 19 Anonymous User

created jan 16 14 anonymous