MySQL Query Moodle Get Absent Users For Specific Category

moodle-absent-users.sql

SELECT 
    `mdl_course`.`id` AS `course_id`, 
    `mdl_course`.`shortname` AS `course_name`, 
    `mdl_user`.`email` AS `email_address`, 
    `mdl_user`.`firstname` AS `first_name`, 
    CASE 
WHEN `mdl_role_assignments`.`roleid` = 1 THEN 'Manager' 
WHEN `mdl_role_assignments`.`roleid` = 2 THEN 'Course creator' 
WHEN `mdl_role_assignments`.`roleid` = 3 THEN 'Teacher' 
WHEN `mdl_role_assignments`.`roleid` = 4 THEN 'Non-editing teacher' 
WHEN `mdl_role_assignments`.`roleid` = 5 THEN 'Student' 
WHEN `mdl_role_assignments`.`roleid` = 6 THEN 'Guest' 
WHEN `mdl_role_assignments`.`roleid` = 7 THEN 'Authenticated user' 
    END AS `role`, 
    TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`lastaccess`), NOW()) AS `days_since_access`, 
    FROM_UNIXTIME(`lastaccess`, '%m/%d/%Y') AS `last_access` 
FROM `mdl_course` 
LEFT OUTER JOIN `mdl_context` ON `mdl_course`.id = `mdl_context`.`instanceid` 
LEFT OUTER JOIN `mdl_role_assignments` ON `mdl_context`.`id` = `mdl_role_assignments`.`contextid` 
LEFT OUTER JOIN `mdl_user` ON `mdl_role_assignments`.`userid` = `mdl_user`.`id` 
LEFT JOIN `mdl_user_lastaccess` ON `mdl_user_lastaccess`.`courseid` = `mdl_course`.`id` 
WHERE `mdl_context`.`contextlevel` = '50' 
AND TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`lastaccess`), NOW()) > -1 
AND TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`lastaccess`), NOW()) < 60 
AND `mdl_course`.`category` = :category_id' 
GROUP BY  `mdl_user`.`id`,`mdl_course`.`id` 
ORDER BY `role`,`lastaccess` DESC