Q&A about the site for gardeners and landscapers
WITH Answers AS ( SELECT * FROM Posts WHERE PostTypeId = 2 -- Answers ) SELECT TOP ##NumberOfQuestions:int?200## Questions.Id AS [Post Link], Questions.Score AS [Q Score], 'site://a/' + CAST(FirstAnswer.Id AS NVARCHAR) + '|' + CAST(DATEDIFF(mi, Questions.CreationDate, FirstAnswer.CreationDate) AS NVARCHAR) AS [Minutes to First Ans], 'site://a/' + CAST(LastAnswer.Id AS NVARCHAR) + '|' + CAST(DATEDIFF(mi, Questions.CreationDate, LastAnswer.CreationDate) AS NVARCHAR) AS [Minutes to Last Ans], 'site://a/' + CAST(AcceptedAnswer.Id AS NVARCHAR) + '|' + CAST(DATEDIFF(mi, Questions.CreationDate, AcceptedAnswer.CreationDate) AS NVARCHAR) AS [Minutes to Accepted Ans], 'site://a/' + CAST(BestAnswer.Id AS NVARCHAR) + '|' + CAST(DATEDIFF(mi, Questions.CreationDate, BestAnswer.CreationDate) AS NVARCHAR) AS [Minutes to Highest-Scored Ans] FROM Posts AS Questions LEFT OUTER JOIN Answers AS FirstAnswer ON FirstAnswer.ParentId = Questions.Id LEFT OUTER JOIN Answers AS LastAnswer ON LastAnswer.ParentId = Questions.Id LEFT OUTER JOIN Answers AS AcceptedAnswer ON AcceptedAnswer.Id = Questions.AcceptedAnswerId LEFT OUTER JOIN Answers AS BestAnswer ON BestAnswer.ParentId = Questions.Id WHERE Questions.PostTypeId = 1 -- Questions AND FirstAnswer.Id = ( SELECT TOP 1 Id FROM Answers WHERE Answers.ParentId = Questions.Id ORDER BY CreationDate ASC ) AND LastAnswer.Id = ( SELECT TOP 1 Id FROM Answers WHERE Answers.ParentId = Questions.Id ORDER BY CreationDate DESC ) AND BestAnswer.Id = ( SELECT TOP 1 Id FROM Answers WHERE Answers.ParentId = Questions.Id ORDER BY Score DESC );