Bumped Posts - New Answer Yet Unresolved

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT 
	BH.*,
    'http://stackoverflow.com/questions/' + CONVERT(varchar,BH.PostID) + '/' AS PostLink,
	Posts.CreationDate, Posts.Score, Posts.ViewCount, Posts.Title, Posts.Tags, Posts.AnswerCount, Posts.CommentCount
FROM
	(SELECT
		PH.PostId,
		MAX(PH.CreationDate) AS LastBump,
		COUNT(1) AS BumpCount,
		(SELECT Max(CreationDate) AS AnswerDate FROM Posts AP WHERE AP.ParentID=PH.PostId AND AP.Score=0) AS LastAnswer
	FROM
		PostHistory PH
	WHERE
		PH.PostHistoryTypeID=50
		AND PH.CreationDate>'1/1/2016'
	GROUP BY
		PostId) BH
LEFT JOIN
	Posts ON Posts.Id=BH.PostId
WHERE 
	Posts.AcceptedAnswerID IS NULL
	AND Posts.Score>=0
	AND LastAnswer>DateAdd(d, -1 * ##DaysPrevious##, GETDATE())
	AND NOT EXISTS (SELECT * FROM Posts AP WHERE AP.ParentID=Posts.Id AND AP.Score>0)
	AND BH.BumpCount>1
ORDER BY
	BumpCount DESC,
	Score DESC

Enter Parameters

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