Answers, Posts, and Views by Date Range and Userid

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

DECLARE
    @user AS int = ##userid?7031230##
,   @startDate AS date = '20180514'
,   @endDate AS date = '20190513';

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.Id = @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