Questions closed as duplicate of unanswered question of different author

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/* domain of the site being queried, e.g. superuser.com */
DECLARE @Domain varchar(40) = ##Domain:string##

CREATE TABLE #duplicates (ClosedPostId INT, OriginalPostId INT, ClosedDate DATETIME)
INSERT #duplicates
SELECT Closed.Id ClosedPostId,

/* question ID of original q parsed from history entry text */
SUBSTRING(
  Text, 
  CHARINDEX('http://' + @Domain + '/questions/', Text)+LEN('http://' + @Domain + '/questions/'),
  CHARINDEX('/', Text, 
    CHARINDEX('http://' + @Domain + '/questions/', Text)
    +LEN('http://' + @Domain + '/questions/')
    )-CHARINDEX('http://' + @Domain + '/questions/', Text)-LEN('http://' + @Domain + '/questions/')
) OriginalPostId,
PH.CreationDate ClosedDate
FROM Posts Closed
JOIN PostHistory PH ON PH.PostId = Closed.Id
WHERE PH.Comment = 'insert duplicate link'

/* some history entries appear to be broken */
AND CHARINDEX('http://' + @Domain + '/questions/', Text) <> 0
AND Closed.ClosedDate IS NOT NULL

CREATE TABLE #unanswered (Id INT)
INSERT #unanswered

SELECT Unanswered.Id
FROM Posts Unanswered
WHERE (
  SELECT COUNT(*) 
  FROM Posts Answers
  WHERE Answers.ParentId = Unanswered.Id 
  AND Answers.Score > 0
) = 0
AND Unanswered.PostTypeId = 1
AND AcceptedAnswerId IS NULL



SELECT TOP 100 ClosedPostId as [Post Link], #duplicates.ClosedDate
FROM #duplicates
JOIN #unanswered ON #unanswered.Id = #duplicates.OriginalPostId
JOIN Posts Originals ON #duplicates.OriginalPostId = Originals.Id
JOIN Posts Duplicates ON #duplicates.ClosedPostId = Duplicates.Id
WHERE Originals.OwnerUserId <> Duplicates.OwnerUserId
ORDER BY #duplicates.ClosedDate DESC

Enter Parameters

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