Is there any way to find out the top posters from a particular month?

Reply

Alex Crome replied on Tue, Aug 26 2008 4:13 AM

rated by 0 users

Verified by Daniel Rae

Any data obtained by Harvest can be obtained through executing a SQL script against your database.

Try the folowing for size.  N.B. requires dates in the British dd/mm/yy format, and only two digit year (although British date shouldnt' be a problem for you )

SELECT     PostAuthor, COUNT(p.PostId) as PostCount
FROM         cs_Posts p
WHERE (p.PostDate < CONVERT(DATETIME, N'31/08/08', 3))
 AND (p.PostDate > CONVERT(DATETIME, N'01/08/06', 3))
 AND (p.IsApproved = 1)
 AND (p.SectionID > 2) -- Exclude PMs (0), Reporting Forums (1) and Deleted Posts(2)
GROUP BY p.PostAuthor
ORDER BY PostCount DESC

This will include all posts on the site (including weblog posts, comments, files etc).  If you want to filter by a specific application type, you can add a AND (p.ApplicationType = X) to the WHERE statement.  (Replace X with the appropiate integer value from cs_Applications)