Eduardo,
Hola.
Utilizo esta consulta para sacar los datos de cursos vacios.
Es difícil saber exactamente cuando cualquier curso está "vacio." Por tanto, esta consulta filtra por una gama de las fechas de modifcacíones. Si algún curso nunca ha sido modificado (timecreated=timemodified), hay -1 en lugar de la diferencia real de días en la columna 'DateDifference' (por ayudar a ordenar).
Trabajo con Moodle 3.1 y el plugin Informes configurables https://moodle.org/plugins/view.php?id=82. Espero que esto funcione y sea útil.
- Randy
SELECT
c.fullname,
CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS 'CourseLink',
DATE_FORMAT(FROM_UNIXTIME(c.timecreated), '%Y-%m-%d %H:%i') AS 'Timecreated',
DATE_FORMAT(FROM_UNIXTIME(c.timemodified), '%Y-%m-%d %H:%i') AS 'Timemodified',
CASE
WHEN c.timecreated = c.timemodified THEN '-1'
ELSE DATEDIFF(FROM_UNIXTIME(c.timemodified),FROM_UNIXTIME(c.timecreated))
END AS 'DateDifference',
COUNT(ue.id) AS Enroled
FROM prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
LEFT JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
WHERE DATEDIFF(FROM_UNIXTIME(c.timemodified),FROM_UNIXTIME(c.timecreated) ) < 60
GROUP BY c.id
HAVING COUNT(ue.id) <= 3
ORDER BY c.fullname