Accepted answers to questions old more than a period

0

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

-- Shows accepted answers to questions old more than a period
-- ResultCount: Number of results to display
-- AnsweringThreshold: The question should have been created [x] months ago

SELECT TOP ##ResultCount:int##
       q.Id as [Post Link],
       q.Score as [Question scored],
       u.Id as [User Link],
       q.CreationDate as [Asked on],
       a.Score as [Answer scored],
       a.CreationDate as [Answered on],
       au.Id as [User Link]
       FROM Posts q
       JOIN Posts a ON q.AcceptedAnswerId = a.Id
       JOIN Users u ON q.OwnerUserId = u.Id
       JOIN Users au ON a.OwnerUserId = au.Id
       WHERE q.PostTypeId = 1
             AND DATEDIFF(month, q.CreationDate, a.CreationDate) >= ##AnsweringThreshold:int##
             AND q.AcceptedAnswerId IS NOT NULL
             ORDER BY q.Id DESC

-- NOTE: this query does not seem to take care of day of creation, so it can
-- return:
--
--  ... | Asked on            | ... | Answered on         | ...
-- -----+---------------------+-----+---------------------+-----
--  ... | 2018-06-28 19:16:18 | ... | 2019-06-06 07:43:02 | ...
--
-- although different between 28th June 2018 and 6th June 2019 is less
-- than 12 months. This is independent of us and is most likely to be a
-- bug with the Microsoft SQL Server; for more details, please refer to
-- https://docs.microsoft.com/sql/t-sql/functions/datediff-transact-sql.

Enter Parameters

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