Trends in questions asked

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/273645/trends-on-stack-overflow

Stack Overflow

Q&A for professional and enthusiast programmers

select *
into #selectedtags
from 
(
      select N'python' as tagname
union select N'c#'
union select N'java'
) as selection

create table #all(creationdate datetime);
declare tag_cur cursor for 
        select tagname 
        from #selectedtags
        order by tagname
declare @curr_tag nvarchar(25)
declare @main_insert nvarchar(max)

open tag_cur;
fetch next from tag_cur into @curr_tag
set @main_insert = 'insert into #all
select dateadd(m,  -datediff(m, p.creationdate, getdate()),getdate())'
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('alter table #all add  [' + @curr_tag + '] nvarchar(25)')
set @main_insert = @main_insert + ', sum(case 
          when t.tagname ='''+ @curr_tag + ''' 
          then 1 
          else 0
          end) as ['+ @curr_tag+']';
fetch next from tag_cur into @curr_tag
END
close tag_cur;
set @main_insert = @main_insert + '
from posts p
inner join posttags pt on pt.postid = p.id
inner join tags t on t.id = pt.tagid
inner join #selectedtags st on st.tagname = t.tagname collate database_default
and p.creationdate < eomonth(getdate(),-1)
group by datediff(m, p.creationdate, getdate())'

print @main_insert
exec (@main_insert)

/*
select dateadd(m,  -datediff(m, p.creationdate, getdate()),getdate())
     , sum(case 
          when t.tagname ='python' 
          then 1 
          else 0
          end) as [Python]
     , sum(case 
          when t.tagname ='java' 
          then 1 
          else 0
          end) as [java]
     , sum(case 
          when t.tagname ='c#' 
          then 1 
          else 0
          end) as [c#]
from posts p
inner join posttags pt on pt.postid = p.id
inner join tags t on t.id = pt.tagid
inner join #selectedtags st on st.tagname = t.tagname collate database_default
and p.creationdate < eomonth(getdate(),-1)
group by datediff(m, p.creationdate, getdate())
order by 1 desc
*/

select * from #all
order by 1 desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results