8

Please login or register to vote for this query.

(click on this box to dismiss)

Identify who first created a tag, and how many questions use it.

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

create table #tago (
TagId int,
TagName NVarchar(25),
TagCount int,
Owner int,
Awarded NVarchar(3),
cpost int,
RevisionLink NVarchar(250))

declare @tid int,
        @tname NVarchar(25),
        @tcnt int,
        @phid int,
        @rcnt int,
        @uid int,
        @awarded int,
        @guid uniqueidentifier,
        @pid int;

declare TAGS cursor for select Id, TagName, "Count" from Tags

open TAGS

fetch next from TAGS into @tid, @tname, @tcnt
while @@FETCH_STATUS = 0
BEGIN
   print 'processing tag ' + @tname;
   
   select @phid = Min(Id)
   from PostHistory
   where PostHistoryTypeId in (3, 6)
     and Text like '%<' + @tname + '>%'

   select @pid = PostId,
          @uid = UserId
   from PostHistory
   where Id = @phid

   select @rcnt = count(distinct RevisionGUID)
   from PostHistory
   where PostId = @pid
     and Id <= @phid
     
   select @awarded = Id
   from Badges
   where UserId = @uid
     and Name = 'Taxonomist'
     and @tcnt >= 50
   
   insert into #tago
   select @tid,
          @tname,
          @tcnt,
          @uid,
          case when @awarded is null then '' else 'Yes' end,
          @pid, 
          'site://revisions/' + cast(@pid as nvarchar) 
             + '/' + cast(@rcnt as nvarchar) + '|Revision ' 
             + cast(@rcnt as nvarchar)
   
   fetch next from TAGS into @tid, @tname, @tcnt
END

select
  TagName,
  TagCount,
  Owner [User Link],
  Awarded,
  cpost as [Post Link],
  RevisionLink as [Revision]
from #tago

view current version

edited may 23 14

created may 23 14 rolfl