0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for professional and enthusiast programmers

declare  cr cursor for 
  select comment
       , text 
       , posthistorytypeid 
  from posthistory ph 
  where posthistorytypeid  in (10,11,12,13)
  declare @c nvarchar(200)
  declare @t nvarchar(2000)
  declare @s tinyint
  declare @v nvarchar(2000)
  declare @p int
 
 create table #cvers(userid int
                   , posthistorytypeid tinyint ) 
 
open cr
fetch next from cr into @c, @t, @s
while  @@FETCH_STATUS = 0  
begin
  declare vt cursor for
    select value
    from string_split(@t, ',')
  open vt
  fetch next from vt into @v
  print '---'
  print @t
  print '>'
  while @@FETCH_STATUS = 0  
  begin
    set @p = charindex( '"Id":', @v)
    if @p>0 
    begin
      print substring(@v, @p+5, len(@v))
      insert into #cvers values (cast(substring(@v, @p+5, len(@v)) as int), @s)
    end  
    fetch next from vt into @v
  end
  close vt
  DEALLOCATE vt
  fetch next from cr into @c, @t, @s
end


select userid as [User Link]
     , reputation
     , [10] +[11]+ [12]+ [13] tot
     , [10] as closed, [11] as reopen, [12] as deleted, [13] as undeleted
from (
select userid, reputation, posthistorytypeid 
  from #cvers
  inner join stackoverflow.dbo.users u on u.id = userid) as st
pivot
(  count(posthistorytypeid) for posthistorytypeid in ([10],[11],[12],[13])
) as pt
order by [10]+[11]+[12]+[13] desc

--select *
--from string_split('jdf,v,d;d;d',',')

fork query

edited oct 21 16

created oct 20 16 rene