Number of questions/answers/comments per day for the past 6 months

0

Please login or register to vote for this query.

(click on this box to dismiss)

Sitecore Meta

Q&A about the site for developers and end users of the Sitecore CMS and multichannel marketing software

DECLARE @commentTypeId tinyint;  
SET @commentTypeId = 255;  

DECLARE @all_posts TABLE
(
    type tinyint null,
    date_created date null
)

insert into @all_posts
select  @commentTypeId type,
        DATEADD(day,0,DATEDIFF(day,0,c.CreationDate)) date_created
from    Comments c 
where   c.CreationDate >= DATEADD(MONTH, -6, GETDATE())

insert into @all_posts
select  PostTypeId type,
         DATEADD(day,0,DATEDIFF(day,0,CreationDate)) date_created
from    PostsWithDeleted
where   CreationDate >= DATEADD(MONTH, -6, GETDATE())
and     (PostTypeId = 1 or PostTypeId = 2)


select  date_created,
        SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS [Questions],
        SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS [Answers],
        SUM(CASE WHEN type = @commentTypeId THEN 1 ELSE 0 END) AS [Comments]
from    @all_posts
group by date_created
order by date_created

Enter Parameters

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