DotNetNuke Forum Count Posts Per User/Month

Do you run a website using the DotNetNuke Forum module? If so, you might find that you want to run a report on users in a specific role to see how many posts those users are posting per month in your forums.

If that’s the case I have just the SQL sample for you!

Here’s some T-SQL if you are using the standard DBO and empty objectQualifier settings in your web.config

SELECT 
 u.username,
 DATEPART(month, createddate) AS Month,
 DATEPART(year, createddate) AS Year,
 COUNT(*) AS PostCount
FROM forum\_posts fp
JOIN users u ON (fp.userid = u.userid)
WHERE fp.userid IN (SELECT userid FROM userroles WHERE roleid=1)
-- uncomment the next line to run for a specific user
-- AND u.Username = 'USERNAME'
GROUP BY DATEPART(month, createddate), DATEPART(year, createddate), u.username
ORDER BY u.username, DATEPART(year, createddate), DATEPART(month, createddate);

And of course, the obligatory tokenized SQL that you can run in the host/sql page

SELECT 
 u.username,
 DATEPART(month, createddate) AS Month,
 DATEPART(year, createddate) AS Year,
 COUNT(*) AS PostCount
FROM {databaseOwner}{objectQualifier}forum\_posts fp
JOIN {databaseOwner}{objectQualifier}users u ON (fp.userid = u.userid)
WHERE fp.userid IN (SELECT userid FROM {databaseOwner}{objectQualifier}userroles WHERE roleid=1)
-- uncomment the next line to run for a specific user
-- AND u.Username = 'USERNAME'
GROUP BY DATEPART(month, createddate), DATEPART(year, createddate), u.username
ORDER BY u.username, DATEPART(year, createddate), DATEPART(month, createddate);