## Analytics and reporting

This discussion has been locked because a year has elapsed since the last post. Please start a new discussion topic.

Hi everyone,

I'm working on a query to be run with the "Ad-hoc database queries" add on.

We have students in groups and groupings in a course, and I'm after a report to display the following data for a single given course (eg: courseid = 2)

+--------------+-------------------+--------------+------------------+------------+
| name         | email             | group        | grouping         | completed  |
+--------------+-------------------+--------------+------------------+------------+
| Fred Smith   | fred@example.com  | Monday Class | Weekday Students | 2013-09-18 |
| Barry Porter | fred@example.com  | Friday Class | Weekday Students |            |
| Sally Jones  | sally@example.com | Sunday Class | Weekend Students | 2013-03-23 |

The name and email come from the mdl_user table.

The group can be worked out based on the course ID (there is only one course)

The grouping should be able to be worked out based on the groupid

The completed is from the timecompleted field in the mdl_course_completions table based on the userid and the courseid.

If anyone could help out, that would be amazing!

Thanks!!

Average of ratings: -

Try this:

SELECT DISTINCT concat(u.firstname, ' ',u.lastname) AS name, u.email, g.name AS groupname, gr.name AS Grouping, DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
),'%Y-%m-%d') AS Completed
FROM prefix_course AS c
JOIN prefix_groups AS g
JOIN prefix_groups_members AS m ON g.id = m.groupid
JOIN prefix_user AS u ON m.userid = u.id
JOIN prefix_groupings_groups AS gg ON g.id = gg.groupid
JOIN prefix_groupings AS gr ON gg.groupingid = gr.id
JOIN prefix_course_completions AS p ON p.course = c.id
WHERE c.id = 3 AND p.userid = u.id
ORDER BY u.firstname

I think that will work. Let me know if it does. Sample of data I get is below. Seems to work okay. In the example there are four groups in two groupings (course.id = 3).

I snagged most of this from queries I did a while ago for groups and completion that I put over in the 2.5 ad hoc queries area. Just had to figure out the groups/groupings link.

Average of ratings: Useful (3)