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)
,datepart(year, createddate)
, count(*)
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
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)
,datepart(year, createddate)
, count(*)
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
username
, datepart(year, createddate)
, datepart(month, createddate)