I am using Bigbluebutton activity plugin from Moodle where students login and join webinar. I tried to take attendance who are present and absent. I have successfully made the way to find who are present but not able to get who are absent as I am not an SQL expert.
Can someone look and give me a hand?
Here is the working code for finding those who attended:
SELECT C.fullname coursename,L.userid,U.username,U.firstname, U.lastname,ra.roleid,L.action,'present' as Attendance,
FROM_UNIXTIME( L.timecreated, '%m-%d-%Y' ) as DATE,
FROM_UNIXTIME( L.timecreated, '%r' ) as TIME
FROM prefix_logstore_standard_log as L
LEFT JOIN prefix_course as C ON L.courseid = C.id left JOIN prefix_user as U on L.userid = U.id
JOIN prefix_role_assignments AS ra ON L.userid = ra.userid
JOIN prefix_role AS r ON r.id = ra.roleid
#WHERE L.action LIKE '%Joined%' OR '%Left%' OR '%Recording%',
WHERE ((L.action LIKE '%Joined%')) AND (ra.roleid=5) AND (ra.roleid NOT IN (3,4))
%%FILTER_STARTTIME:L.timecreated:>%% %%FILTER_ENDTIME:L.timecreated:<%%
group by U.username,L.courseid
order by L.courseid