Weekly trends

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

select *
into #selectedtags
from 
(
-- add unions for tags as much as you like
      select N'flutter' as tagname
union select N'react-native'
union select N'kotlin'
union select N'swift'

) as selection


declare tag_cur cursor for 
        select tagname 
        from #selectedtags
        order by tagname
declare @curr_tag nvarchar(25)
declare @main_insert nvarchar(max)

set @main_insert = 'select dateadd(ww,  -datediff(ww, p.creationdate, getdate()),getdate())'

open tag_cur;
fetch next from tag_cur into @curr_tag
WHILE @@FETCH_STATUS = 0
BEGIN
-- use a different aggregate here if needed
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 <= dateadd(wk, datediff(wk, 6, getdate()), 6)
        and year(p.creationdate) >= 2018
group by datediff(ww, p.creationdate, getdate()) -- whole months
order by 1 desc'

print @main_insert
exec (@main_insert)

/*
-- this is what the query looks like that gets dynamically generated
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
*/

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results