Moodle Query to get Attendance Student wise Course Wise

moodle_query_get_attendance_studentwise_coursewise.sql

-- Moodle get student wise attendence for each courses
-- To filter data you can put the parent course id and start {start_date_epoch} and end {end_date_epoch} in where clause
select 
   l.id as logid,
   c.fullname as course,
   CONCAT(FROM_UNIXTIME(s.sessdate,"%d %b, %Y %h:%i %p"), ' - ', FROM_UNIXTIME(s.sessdate + s.duration ,"%h:%i %p")) as session_title,
   -- s.description as session_desc,
   u.id as student_id, 
   u.username, 
   u.firstname as first_name, 
   u.lastname as last_name,
   st.acronym,  
   CASE
    WHEN l.studentid = l.takenby THEN "Student"
    ELSE "Teacher"
	END as taken_by,
   FROM_UNIXTIME(l.timetaken) as time_taken, 
   l.remarks
   -- l.ipaddress
from mdl_attendance_log as l
left join mdl_attendance_statuses as st on st.id = l.statusid
left join mdl_user as u on u.id = l.studentid
left join mdl_attendance_sessions as s on s.id = l.sessionid
left join mdl_attendance as a on a.id = s.attendanceid
left join mdl_course as c on c.id = a.course
left join mdl_course_categories as cc on cc.id = c.category
where s.sessdate between {start_date_epoch} and {end_date_epoch} and cc.path like '/{parent_course_category_id}/%'
order by l.sessionid, l.id desc;