0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the trend for your favourite tag

Q&A about the site for enthusiasts and power users of Windows Phone OS

-- How many posts per month for a tag?
-- Shows the trend for your favourite tag

DECLARE @TagName varchar(255) = '##TagName##'

SELECT r.YearMonth,
       (SELECT COUNT(*)
        FROM Posts
        WHERE Posts.Tags
        LIKE '%<##TagName##>%'
        AND DATEADD(month,
                    DATEPART(month,
                             Posts.CreationDate),
                    DATEADD(year,
                            DATEPART(year,
                                     Posts.CreationDate),
                            0))
         <= DATEADD(month,r.PMonth,DATEADD(year,r.PYear, 0))) as Posts
FROM (
       SELECT DISTINCT YEAR(Posts.CreationDate) as PYear,
              MONTH(Posts.CreationDate) as PMonth,
              DATEADD(MONTH, DATEDIFF(MONTH, 0, Posts.CreationDate), 0) as YearMonth 
       FROM Posts
       WHERE Posts.Tags LIKE '%<##TagName##>%'
     ) AS r
GROUP BY r.Pyear, r.PMonth, r.YearMonth
ORDER BY r.PYear, r.PMonth, r.YearMonth

fork query

edited may 2 12 anonymous

created dec 13 10 prof3ta