Hi I am producing a number of reports at present and some of these reports are pulling enrolments out for the last 30 days and showing daily figures. However I want to be able to still show the dates for any dates which have no enrolments as 0. At present mysql just skips dates if there is no data for these.
I have been looking around and the best option seems to be to create a calendar table, however I was trying to find a method which would not involve me having to create an extra table within Moodles DB. Does anyone know if there is a better technique or is the calendar function the standard for this sort of querying?
Below is the query which I am running for test purposes just using a hard coded course id at present. Any advice on this area would be greatly appreciated.
Thanks
Scott
$daily_enrolments = $DB->get_records_sql("SELECT DATE(FROM_UNIXTIME(ue.timecreated)) Date, COUNT(*) enrolments FROM {user_enrolments} ue
LEFT JOIN {enrol} e ON ue.enrolid = e.id WHERE e.courseid = 5 AND
(FROM_UNIXTIME(ue.timecreated) > DATE_SUB(now(), INTERVAL 30 DAY))
GROUP BY DATE(FROM_UNIXTIME(ue.timecreated)) ORDER BY Date");