Answers, Posts, and Views by Date Range and DisplayName (MVP)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE
    @user varchar(50) = 'billinkc'
,   @startDate date = '04-01-2018'
,   @endDate AS datetime = '03/31/2019';

SELECT
    pt.Name AS ActivityName
,   COUNT_BIG(*) AS AnswersProvided
,   COUNT_BIG(   CASE
                     WHEN parent.AcceptedAnswerId = p.ID THEN 1
                     ELSE NULL
                 END
             ) AS AnswersAccepted
,   SUM(p.Score) AS Score
,   SUM(COALESCE(p.ViewCount, parent.ViewCount)) AS Views
,   CONCAT(DATENAME(m, @startDate), ' ', DATENAME(YEAR, @startDate)) AS Start
,   CONCAT(DATENAME(m, @endDate), ' ', DATENAME(YEAR, @endDate)) AS Finish
--,   DATENAME(YEAR, p.CreationDate) AS PostYear
--,   DATENAME(m, p.CreationDate) AS PostMonth
FROM
    Posts AS p
    LEFT JOIN
        Posts AS parent
        ON p.ParentId = parent.Id
    JOIN
        Users AS u
        ON p.OwnerUserId = u.Id
    JOIN
        PostTypes AS pt
        ON p.PostTypeId = pt.Id
WHERE
    u.DisplayName = @user
    AND p.CreationDate BETWEEN @startDate AND @endDate
GROUP BY ROLLUP(pt.Name)
/*
-- breakdown by month
GROUP BY
    pt.Name
,   DATENAME(YEAR, p.CreationDate)
,   DATENAME(m, p.CreationDate);
*/
--order by  CAST(CONCAT(PostYear, '-', PostMonth, '-', 1) as date)

Enter Parameters

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