I've written a query " List of Questions with only 1 Remaining Delete Vote Required to Delete ". One of the several problems with this query is that it includes questions that I have already voted to delete.
Is there a way I could omit such questions from my query? The UsedId column of the Votes table is never set.
Note that I'm a 10k user.
; WITH CLOSEDQUESTIONS(Id, Score, AScore, VotesNeededToDelete) AS ( SELECT-- top 1000 p.Id, p.Score, SUM(a.Score) AS AScore, CASE WHEN 3 + ((p.Score + Sum(a.Score)) /20)>10 THEN 10 ELSE 3 + ((p.Score + Sum(a.Score)) /20) END as VotesNeededToDelete FROM Posts as p INNER JOIN Posts as a ON a.ParentID = p.Id WHERE p.PostTypeId=1 AND p.ClosedDate IS NOT NULL AND EXISTS (SELECT * FROM Votes AS v WHERE v.PostId = p.Id) GROUP BY p.Id, p.Score ) SELECT TOP 100 Q.Id AS [Post Link], MAX(Q.VotesNeededToDelete) - COUNT(v.VoteTypeId) AS VotesToGo FROM CLOSEDQUESTIONS AS Q INNER JOIN Votes AS v ON v.PostId = Q.Id WHERE v.VoteTypeId=10 GROUP BY Q.Id HAVING MAX(Q.VotesNeededToDelete) - COUNT(v.VoteTypeId)=1