Daily Archives: August 27, 2012

I couldn’t choose a post/thread count report / statistics tool while in the vBulletin admin interface, so I threw together this query, which reports a person’s thread counts along with post counts collected by month since the forum was designed: Code: DECIDE ON p. period, p. discussions, t. threads COMING FROM (SELECT FROM_UNIXTIME(dateline, ‘%Y %M’) WHEN period, FROM_UNIXTIME(dateline, ‘%Y%m’) WHEN order_by, COUNT(*) WHEN posts FROM article GROUP BY period) BECAUSE p LEFT ENROLL IN (SELECT FROM_UNIXTIME(dateline, ‘%Y %M’) WHEN period, COUNT(*) WHEN threads FROM line GROUP BY period) BECAUSE t ON p. period of time = t. period of time ORDER BY p. order_by I figured other vBulletin owners could find it valuable. And here’s an extra report that exhibits current month’s posts and threads by way of day: Code: SELECT p. period of time, p. posts, capital t. threads FROM (SELECT FROM_UNIXTIME(dateline, ‘%Y-%m-%d’) WHEN period, COUNT(*) WHEN posts FROM article WHERE FROM_UNIXTIME(dateline, ‘%Y-%m’) = DATE_FORMAT(CURRENT_TIMESTAMP, ‘%Y-%m’) NUMBER BY period) BECAUSE p LEFT ENROLL IN (SELECT FROM_UNIXTIME(dateline, ‘%Y-%m-%d’) WHEN period, COUNT(*) WHEN threads FROM line WHERE FROM_UNIXTIME(dateline, ‘%Y-%m’) = DATE_FORMAT(CURRENT_TIMESTAMP, ‘%Y-%m’) NUMBER BY period) BECAUSE t ON p. period of time = t. period of time ORDER BY p. period … Continue reading →