0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for administrators and users of the CiviCRM Constituent Relationship Management software

DECLARE @dsql nvarchar(max), @params nvarchar(max);

SET @params=N'@PostTypeId int, @StartDate datetime, @EndDate datetime';

SET @dsql=N'
    SET NOCOUNT ON;
    SELECT Id, AcceptedAnswerId, AnswerCount, CreationDate, ParentId, Score
    INTO #temp
    FROM dbo.Posts
    WHERE PostTypeId=@PostTypeId and
        CreationDate between @StartDate and @EndDate;
    create clustered index lol on #temp (Id);
    select t.AcceptedAnswerId,
        SUM(t.AnswerCount) as SumAnswerCount,
        MIN(t.CreationDate) as MinCreationDate,
        SUM(t.Score) as SumScore,
        SUM(p.FavoriteCount) as SumFavoriteCount,
        SUM(p.ViewCount) as SumViewCount
    FROM #temp AS t
    JOIN dbo.Posts as p on t.Id= p.Id
    GROUP by t.AcceptedAnswerId;
'

exec sp_executesql @dsql, @params, @PostTypeId=5, @StartDate='2012-12-01 00:38:07.173', @EndDate='2012-12-01 00:39:07.173';

exec sp_executesql @dsql, @params, @PostTypeId=2, @StartDate='2012-04-01 00:38:07.173', @EndDate='2012-12-01 00:39:07.173';

view current version

edited dec 9

created dec 9 anonymous