Hi,
We are using the ad-hoc reports.
We need to know the number of users how uses Moodle on a yearly bases.
We found one query that gives monthly but we need yearly one.
This the one available:
Monthly Usage by Role
This report shows a distinct count of users by their role, accessing your site. Each instance of user and role is counted once per month, no matter how many courses they access. We use this to show the total number of students and teachers accessing our site.
SELECT MONTH(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_month, YEAR(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_year, prefix_role.name AS user_role, COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users FROM prefix_stats_user_monthly INNER JOIN prefix_role_assignments ON prefix_stats_user_monthly.userid = prefix_role_assignments.userid INNER JOIN prefix_context ON prefix_role_assignments.contextid = prefix_context.id INNER JOIN prefix_role ON prefix_role_assignments.roleid = prefix_role.id WHERE prefix_context.contextlevel = 50 AND `prefix_stats_user_monthly`.`stattype` = 'activity' AND prefix_stats_user_monthly.courseid <>1 GROUP BY MONTH(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), YEAR(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), prefix_stats_user_monthly.stattype, prefix_role.name ORDER BY YEAR(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), MONTH(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), prefix_role.name
We need the same result but on a yearly bases to know exactly how many users where using the site every year.
Thank you indeed.
Salim