6

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 linguists, teachers and students of the Korean language

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,
        @cdate DateTime,
        @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,
          @cdate = CreationDate,
          @uid = UserId
   from PostHistory
   where Id = @phid

   select @rcnt = count(distinct RevisionGUID)
   from PostHistory
   where PostId = @pid
     and Id <= @phid
   
   insert into #tago
   select @tid,
          @tname,
          @tcnt,
          @uid,
          @cdate,
          @pid, 
          'site://revisions/' + cast(@pid as nvarchar) + '/' + cast(@rcnt as nvarchar)
   
   fetch next from TAGS into @tid, @tname, @tcnt
END

select * from #tago

view current version

edited may 23 14

created may 23 14 rolfl