The new Stats feature allows you to run some SQL statements periodically and save the results for later viewing. You can also use this capability to run some maintenance related SQL statements (see below for example).
Here are some useful SQL statements you can use:
Number of registered users last 30 days Declare @dateBegin datetime
Set @dateBegin = dateAdd(m, -1, GetDate())
SELECT count(*), 'Number of registered users between ' + Convert(varchar, @dateBegin, 101) + ' and ' + Convert(varchar, GetDate(), 101), ''
FROM pgd_members
WHERE dateSignup between @dateBegin and GetDate()
Number of topics over the last 30 days Declare @dateBegin datetime
Set @dateBegin = dateAdd(m, -1, GetDate())
SELECT count(*), 'Number of new topics posted between ' + Convert(varchar, @dateBegin, 101) + ' and ' + Convert(varchar, GetDate(), 101), ''
FROM pgd_threads
WHERE dateCreated between @dateBegin and GetDate()
Number of active users over the last 30 days Declare @dateBegin datetime
Set @dateBegin = dateAdd(m, -1, GetDate())
SELECT count(*), 'Number of active users between ' + Convert(varchar, @dateBegin, 101) + ' and ' + Convert(varchar, GetDate(), 101), ''
FROM pgd_members
WHERE lastLogin between @dateBegin and GetDate()
Number of unique members that posted over the last 30 days Declare @dateBegin datetime
Set @dateBegin = dateAdd(m, -1, GetDate())
SELECT count(*), 'Number of unique members that posted between ' + Convert(varchar, @dateBegin, 101) + ' and ' + Convert(varchar, GetDate(), 101), ''
FROM pgd_members
WHERE lastPostDate between @dateBegin and GetDate()
Messages posted each day the last 30 days Declare @dateBegin datetime
Set @dateBegin = dateAdd(m, -1, GetDate())
SELECT Count, Date, ''
FROM (
SELECT count(*) as Count, Convert(varchar, dateCreated, 101) as Date
FROM pgd_messages
where dateCreated Between @dateBegin and GetDate()
Group By Convert(varchar, dateCreated, 101)
) T
ORDER By Date
Total topics posted each day the last 30 days Declare @dateBegin datetime
Set @dateBegin = dateAdd(m, -1, GetDate())
SELECT Count, Date, ''
FROM (
SELECT count(*) as Count, Convert(varchar, dateCreated, 101) as Date
FROM pgd_threads
where dateCreated Between @dateBegin and GetDate()
Group By Convert(varchar, dateCreated, 101)
) T
ORDER By Date
(maintenance related) Remove subscription for users who have not logged on for a year Begin Tran
Delete pgd_subscription where mem in (
SELECT mem from pgd_members where lastLogin < dateadd (year, -1, GetDate()) and mem > 0
)
SELECT @@rowcount, 'Deleted On: ' + Cast (GetDate() as varchar(50)), ''
Commit Tran