WITH moodle AS (
SELECT 
        c.id AS course_id,
        c.shortname,
        c.fullname AS asignatura,
        s.id AS session_id,
        FROM_UNIXTIME(s.sessdate, '%Y-%m-%d %H:%i:%s') AS fecha_sesion,
        u.id AS user_id,
        u.firstname,
        u.lastname,
        u.email,
        st.acronym AS estado,
        st.description AS descripcion_estado
    FROM mdl_attendance_sessions s
    JOIN mdl_attendance a ON s.attendanceid = a.id
    JOIN mdl_course c ON a.course = c.id
    JOIN mdl_attendance_log l ON s.id = l.sessionid
    JOIN mdl_user u ON l.studentid = u.id
    JOIN mdl_attendance_statuses st ON l.statusid = st.id
    WHERE c.shortname LIKE '01/26/%' COLLATE utf8mb4_unicode_ci AND (FROM_UNIXTIME(s.sessdate, '%Y-%m-%d %H:%i:%s') > '2026-04-22 00:00:00' )
    AND c.shortname NOT LIKE '%Eliminar%'
    ORDER BY s.sessdate DESC, u.lastname
)
SELECT shortname, asignatura, REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX(shortname,'/',-2) ,'/',1), '(',' (') AS materia_codigo, 
SUBSTR(shortname,7,3) AS sede_codigo, 
SUBSTR(shortname,11,3) AS carrera_codigo, 
CONCAT('20',SUBSTR(shortname,4,2),'-S',SUBSTR(shortname,2,1)) AS gestion, fecha_sesion, descripcion_estado, email   FROM moodle;0 resultados