0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for gardeners and landscapers

DECLARE @tagName NVARCHAR(255) = 'google-app-maker'
-- TagName: Tag name of the question.
DECLARE @beginDate DATETIME = '2016-01-01'
DECLARE @endDate DATETIME = '2020-12-31'

SELECT T.Month,
  coalesce(sum(ViewCount) ,0) as ViewCount,
  coalesce(sum(AnswerCount) ,0) as AnswerCount, 
  coalesce(sum(CommentCount) ,0) as CommentCount,
  coalesce(sum(FavoriteCount) ,0) as FavoriteCount,
  coalesce(sum(Score) ,0) as Score
FROM
(SELECT
  CAST(
    CAST(DATEPART(YEAR, CreationDate) AS NVARCHAR) + '-' + 
    CAST(DATEPART(MONTH, CreationDate) AS NVARCHAR) + '-01'
      AS DATETIME) AS 'Month',
      p.ViewCount, p.AnswerCount, p.CommentCount, p.FavoriteCount, p.Score
FROM Posts p
  INNER JOIN PostTags pt ON p.Id = pt.PostId
  INNER JOIN Tags t ON t.Id = pt.TagId
  WHERE (p.PostTypeId = 1) AND (t.TagName = @tagName)
  AND (p.CreationDate BETWEEN @beginDate AND @endDate)
GROUP BY 
  DATEPART(YEAR, CreationDate), 
  DATEPART(MONTH, CreationDate),
  p.ViewCount, p.AnswerCount, p.CommentCount, p.FavoriteCount, p.Score) T
GROUP BY T.Month
ORDER BY T.Month ASC

fork query

created apr 20 21 Felipe.Soupinski