0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for active researchers, academics and students of physics

--Get the Tag IDs that you are interested in for the Tags table
SELECT Id, TagName 
INTO #MyTags
FROM Tags
WHERE 
  TagName IN ('swift','objective-c') 
  --TagName IN ('android', 'ipad', 'iphone', 'blackberry', 'windows-mobile') 
  --TagName IN ('sql-server', 'oracle', 'mysql', 'mongodb', 'postgresql') 
  --TagName IN ('javascript', 'java', 'c#', 'vb.net', 'ruby-on-rails', 'ruby', 'php') 
  --TagName IN ('bing-maps', 'google-maps') 
  --TagName IN ('opengl', 'cuda')
  --TagName IN ('fody', 'postsharp')


--Easily change date parameters
DECLARE @StartDate DATETIME = '2012-1-1'
        ,@EndDate DATETIME = DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), -1) -- First Day Of current Month

--
SELECT RoundToWeek, TagName, TagCount
FROM 
(
  SELECT  
    DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0) RoundToWeek
    , COUNT(PostTags.TagID) TagCount
    , TagID
  FROM PostTags
  LEFT JOIN Posts ON
    PostTags.PostId = Posts.Id
  WHERE 
    PostTags.TagID IN (SELECT Id FROM #MyTags)
    AND CreationDate > @StartDate
    AND CreationDate < @EndDate
  GROUP BY
    DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0)
    , PostTags.TagID
)TagCountPerWekk
LEFT JOIN Tags ON
  TagCountPerWekk.TagId = Tags.Id
ORDER BY RoundToWeek DESC, TagName ASC

fork query

edited dec 13 16 anonymous

created feb 24 16 SamtheBrand