Hello, Can anybody help me PLEASE?
I'm trying to make the following query, but managed not find the error
SELECT
u.lastname AS 'LASTNAME',
u.firstname AS 'NAME',
u.email AS 'EMAIL',
gi.itemname AS 'ITEM',
c.fullname AS 'COURSE',
g.name AS 'GROUP',
ROUND(gg.finalgrade,2) AS 'NOTE',
FROM_UNIXTIME(gg.timecreated, '%d/%m/%Y %H:%i') AS 'CREATE'
FROM prefix_course AS c
JOIN prefix_groups AS g ON c.id = g.courseid
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance'
AND
( u.city ='concentro' OR u.city ='CONCENTRO')
AND
(gg.timecreated!='')
AND
(gi.itemname!='Ejercicio: Subir tarea en archivo')
AND
(gi.itemname!='Ejercicio: Tarea de texto en línea')
AND
(FROM_UNIXTIME(gg.timecreated) LIKE '%2015%')
%%FILTER_COURSES:c.id%%
%%FILTER_USERS:u.lastname%%
ORDER BY gg.timecreated DESC, c.fullname
I had difficulty g.name add the table as I need to get the name of the group to which the student belongs.
My moodle version is 2.3
thanks for the attention.
.
SELECT
u.lastname AS 'LASTNAME',
u.firstname AS 'NAME',
u.email AS 'EMAIL',
gi.itemname AS 'ITEM',
c.fullname AS 'COURSE',
g.name AS 'GROUP',
ROUND(gg.finalgrade,2) AS 'NOTE',
FROM_UNIXTIME(gg.timecreated, '%d/%m/%Y %H:%i') AS 'CREATE'
FROM prefix_course AS c
JOIN prefix_groups AS g ON c.id = g.courseid
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance'
AND
( u.city ='concentro' OR u.city ='CONCENTRO')
AND
(gg.timecreated!='')
AND
(gi.itemname!='Ejercicio: Subir tarea en archivo')
AND
(gi.itemname!='Ejercicio: Tarea de texto en línea')
AND
(FROM_UNIXTIME(gg.timecreated) LIKE '%2015%')
%%FILTER_COURSES:c.id%%
%%FILTER_USERS:u.lastname%%
ORDER BY gg.timecreated DESC, c.fullname
I had difficulty g.name add the table as I need to get the name of the group to which the student belongs.
My moodle version is 2.3
thanks for the attention.
.