Attendance sql query

Attendance sql query

by Levon Muradyan -
Number of replies: 3

Hi

I need to export all attendance records, per student, per session. I found following sql query

SELECT DISTINCT att.course AS courseid,c.shortname AS Nomducours,att.name AS Module,attsess.id AS sessionid,
attlog.studentid AS studentid,u.firstname AS Prenom,u.lastname AS Nom,attlog.id AS logid,attlog.statusid AS statusid,attsta.description AS description
FROM mdl_attendance AS att
JOIN mdl_attendance_sessions AS attsess ON attsess.attendanceid = att.id
JOIN mdl_attendance_log AS attlog ON attlog.sessionid = attsess.id
JOIN mdl_attendance_statuses AS attsta ON attsta.id = attlog.statusid
JOIN mdl_user AS u ON u.id = attlog.studentid
JOIN mdl_course AS c ON c.id = att.course
ORDER BY att.course,attsess.id,attlog.statusid;


Can somebody help me to modify that so it will be show the date, similar to the way it shows in the regular attendance export file?


Sincerley,

Levon.


Average of ratings: -
In reply to Levon Muradyan

Re: Attendance sql query

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers
Add this to your select statement DATE_FORMAT(FROM_UNIXTIME(attsess.sessdate),'%d %M %Y')AS Date
Average of ratings: -
In reply to Emma Richardson

Re: Attendance sql query

by JHON JAIRO PINEDA MUÑOZ -

Hola me podrían indicar si es posible como queda el query completamente con lo que menciona Emma, soy nuevo en moodle y me gustaría saber como queda completo para que saque la fecha... Gracias

Average of ratings: -
In reply to Levon Muradyan

Re: Attendance sql query

by Brandon Martel -
Hello friend,

I changed the code so that it will present the dates properly, I've also added a few things like the instructors email, etc. Hope this helps!

SELECT DISTINCT u.firstname, u.lastname, c.shortname AS 'class', attsess.sessdate as 'class date',
attsta.description, attsess.lasttakenby as 'instructor', attlog.remarks, attsta.grade, attsta.deleted,
FROM_UNIXTIME (attsess.sessdate, '%D %M %Y') AS 'Class Date', (SELECT u.username FROM mdl_user as u
WHERE exists
(
SELECT attsess.id from mdl_attendance_sessions
WHERE u.id=attsess.lasttakenby
)) as 'instructor'
FROM mdl_attendance AS att
JOIN mdl_attendance_sessions AS attsess ON attsess.attendanceid = att.id
JOIN mdl_attendance_log AS attlog ON attlog.sessionid = attsess.id
JOIN mdl_attendance_statuses AS attsta ON attsta.id = attlog.statusid
JOIN mdl_user AS u ON u.id = attlog.studentid
JOIN mdl_course AS c ON c.id = att.course
ORDER BY att.course, attsess.id, attlog.statusid
Average of ratings: -