0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for members of the workforce navigating the professional setting

select TagName,
  (
    select count(TagName)
    from Tags
    join PostTags on PostTags.TagId = Tags.Id
    join Posts on Posts.Id = PostTags.PostId
    where Posts.CreationDate > DATEADD(year, -6, GetDate())
      and Posts.CreationDate < DATEADD(year, -5, GetDate())
    and TagName = allTheTags.TagName
    group by Tags.TagName
  ) as '2007',
  (
    select count(TagName)
    from Tags
    join PostTags on PostTags.TagId = Tags.Id
    join Posts on Posts.Id = PostTags.PostId
    where Posts.CreationDate > DATEADD(year, -5, GetDate())
      and Posts.CreationDate < DATEADD(year, -4, GetDate())
    and TagName = allTheTags.TagName
    group by Tags.TagName
  ) as '2008',
  (
    select count(TagName)
    from Tags
    join PostTags on PostTags.TagId = Tags.Id
    join Posts on Posts.Id = PostTags.PostId
    where Posts.CreationDate > DATEADD(year, -4, GetDate())
      and Posts.CreationDate < DATEADD(year, -3, GetDate())
    and TagName = allTheTags.TagName
    group by Tags.TagName
  ) as '2009',
  (
    select count(TagName)
    from Tags
    join PostTags on PostTags.TagId = Tags.Id
    join Posts on Posts.Id = PostTags.PostId
    where Posts.CreationDate > DATEADD(year, -3, GetDate())
      and Posts.CreationDate < DATEADD(year, -2, GetDate())
    and TagName = allTheTags.TagName
    group by Tags.TagName
  ) as '2010',
  (
    select count(TagName)
    from Tags
    join PostTags on PostTags.TagId = Tags.Id
    join Posts on Posts.Id = PostTags.PostId
    where Posts.CreationDate > DATEADD(year, -2, GetDate())
      and Posts.CreationDate < DATEADD(year, -1, GetDate())
    and TagName = allTheTags.TagName
    group by Tags.TagName
  ) as '2011',
  (
    select count(TagName)
    from Tags
    join PostTags on PostTags.TagId = Tags.Id
    join Posts on Posts.Id = PostTags.PostId
    where Posts.CreationDate > DATEADD(year, -1, GetDate())
    and TagName = allTheTags.TagName
    group by Tags.TagName
  ) as '2012'
from Tags allTheTags
where allTheTags.TagName like 'c++'
  or allTheTags.TagName like 'c'    
  or allTheTags.TagName like 'c#'  
  or allTheTags.TagName like 'perl'
  or allTheTags.TagName like 'php'
  or allTheTags.TagName like 'java'
  or allTheTags.TagName like 'objective-c'
  or allTheTags.TagName like 'python'
  or allTheTags.TagName like 'ruby'
  or allTheTags.TagName like 'javascript' 
  or allTheTags.TagName like 'delphi'
  or allTheTags.TagName like 'lisp'
  or allTheTags.TagName like 'logo'
  or allTheTags.TagName like 'pascal'
order by allTheTags.TagName

view current version

edited jul 2 12

created jul 2 12 mnr