1

Please login or register to vote for this query.

(click on this box to dismiss)

Returns all the past N questions with bounties, along with some basic stats about them. 'Closed' bounties were not awarded to any answer, for whatever reason.

Q&A about the site for gardeners and landscapers

SELECT TOP ##number?100##
  --number: Number of questions to query: 
  p.Id AS [Post Link],
  p.Score AS 'Score',
  p.AnswerCount AS 'Answers',
  bs.BountyAmount AS 'Amount',
  convert(varchar(10), bs.CreationDate, 120) AS 'Bounty Start',
  convert(varchar(10), bc.CreationDate, 120) AS 'Bounty Close',
  (CASE WHEN bc.Id IS NULL THEN 'Active' 
        WHEN bc.parentid IS NOT NULL THEN 'Awarded'
        ELSE 'Closed' END) AS 'Status'
        ,
  (CASE WHEN bc.parentid IS NOT NULL THEN bc.answerid 
        ELSE NULL END) AS [Post Link]
FROM Posts p
INNER JOIN Votes bs
    ON p.Id = bs.PostId
    AND bs.VoteTypeId = 8  -- BountyStart
LEFT OUTER JOIN ( 
    SELECT COALESCE(p.ParentId, p.Id) AS id,
           bc.CreationDate, 
           p.ParentId AS parentid,
           p.Id AS answerid
      FROM Posts p
      JOIN Votes bc
        ON p.Id = bc.PostId
       AND bc.VoteTypeId = 9 -- BountyClose
) bc ON bc.id = p.id
ORDER BY bs.CreationDate DESC

fork query

edited jul 12 16 E.P.

created jul 27 15 Bill the Lizard