count of solved questions grouped by the time

0

Please login or register to vote for this query.

(click on this box to dismiss)

SharePoint

Q&A for SharePoint enthusiasts

SELECT 
    CASE
        WHEN DATEDIFF(HOUR, q.CreationDate, a.CreationDate) <= 1 THEN '1 hour or less'
        WHEN DATEDIFF(HOUR, q.CreationDate, a.CreationDate) <= 5 THEN '1-5 hours'
        WHEN DATEDIFF(HOUR, q.CreationDate, a.CreationDate) <= 24 THEN '5-24 hours'
        WHEN DATEDIFF(DAY, q.CreationDate, a.CreationDate) <= 7 THEN '1-7 days'
        ELSE 'More than 7 days'
    END AS TimeRange,
    COUNT(*) AS SolvedQuestionsCount
FROM Posts q
JOIN Posts a ON q.AcceptedAnswerId = a.Id  -- Join to link questions with their accepted answers
WHERE q.PostTypeId = 1  -- Filter for questions
GROUP BY 
    CASE
        WHEN DATEDIFF(HOUR, q.CreationDate, a.CreationDate) <= 1 THEN '1 hour or less'
        WHEN DATEDIFF(HOUR, q.CreationDate, a.CreationDate) <= 5 THEN '1-5 hours'
        WHEN DATEDIFF(HOUR, q.CreationDate, a.CreationDate) <= 24 THEN '5-24 hours'
        WHEN DATEDIFF(DAY, q.CreationDate, a.CreationDate) <= 7 THEN '1-7 days'
        ELSE 'More than 7 days'
    END
ORDER BY SolvedQuestionsCount DESC;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results