0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

-- user: E.g. Shaun Luttin
DECLARE @user varchar(50);
SELECT @user = ##user:string##;

-- startDate: YYYY-MM-DD
DECLARE @startDate DATETIME;
SELECT @startDate = ##startDate:string##;

-- endDate: YYYY-MM-DD
DECLARE @endDate DATETIME;
SELECT @endDate = ##endDate:string##;

SELECT
  pt.Name,
  Posts = COUNT(*),
  Views = SUM(COALESCE(p.ViewCount, parent.ViewCount)),
  Start = FORMAT(@startDate, 'MMM-yyyy'),
  Finish = FORMAT(@endDate, 'MMM-yyyy')
FROM Posts p
LEFT JOIN Posts parent ON p.ParentId = parent.Id
JOIN Users u ON p.OwnerUserId = u.Id
JOIN PostTypes pt ON p.PostTypeId = pt.Id
WHERE u.DisplayName = @user
AND p.CreationDate BETWEEN @startDate AND @endDate
GROUP BY ROLLUP(pt.Name)

fork query

edited jan 28 Shaun Luttin

created mar 31 16 anonymous