0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for students, teachers, polyglots, and anyone interested in the techniques of second-language acquisition

WITH yearmonth 
     AS (SELECT y.number year, 
                m.number month 
         FROM   master..spt_values y 
                CROSS JOIN master..spt_values m 
         WHERE  y.type = 'P' 
                AND y.number >= 2006 
                AND m.type = 'P' 
                AND m.number >= 1 
                AND m.number <= 12) 
SELECT yearmonth, 
       [marketing-cloud], 
       ampscript, 
       ssjs
FROM   (SELECT t.tagname, 
               cast(Cast(ym.year AS CHAR(4)) + CASE WHEN ym.month < 10 THEN '0' 
               ELSE 
                      '' END 
                      + Cast(ym.month AS CHAR(2)) as int) yearmonth, 
               p.id 
        FROM   posts p 
               INNER JOIN posttags pt 
                       ON p.id = pt.postid 
               INNER JOIN tags t 
                       ON pt.tagid = t.id 
               INNER JOIN yearmonth ym 
                       ON Year(p.creationdate) = ym.year 
                          AND Month(p.creationdate) = ym.month 
        WHERE  t.tagname IN ( 'marketing-cloud', 'ampscript', 'ssjs')) 
       AS data 
       PIVOT ( Count(id) 
             FOR tagname IN ([marketing-cloud], 
                             ampscript, 
                             ssjs) ) AS pivottable 
ORDER  BY yearmonth

fork query

edited nov 29 anonymous

created may 20 21 Adam Spriggs