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 for aircraft pilots, mechanics, and enthusiasts

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

declare @tid int,
        @tname NVarchar(25),
        @tcnt int,
        @phid int,
        @rcnt int,
        @uid int,
        @dname NVarchar(40),
        @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,
          @dname = case when UserId is null then
               UserDisplayName 
             else
             (select DisplayName from users where Id = ph.UserId)
             end
   from PostHistory ph
   where Id = @phid

   select @rcnt = count(distinct RevisionGUID)
   from PostHistory
   where PostId = @pid
     and Id <= @phid
     and PostHistoryTypeId < 10
     
   select @awarded = count (Id)
   from Badges
   where UserId = @uid
     and Name = 'Taxonomist'
     and @tcnt >= 50
   
   insert into #tago
   select @tid,
          @tname,
          @tcnt,
          @uid,
          @dname,
          case when @awarded = 0 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],
  Display [Name],
  Awarded,
  cpost as [Post Link],
  RevisionLink as [Revision]
from #tago

view current version

edited may 23 14

created may 23 14 rolfl