I am trying to generate a report and later have the user autogenerate within the course and download as CSV, a list of users that have completed a course given it's short name and date of completion. If there isn't an easy way out of the box, does anyone have a similar SQL query I can run?
I created this query to test:
SELECT dbo.mdl_user.username, dbo.mdl_user_info_data.data AS EmployeeNum, dbo.mdl_user.firstname, dbo.mdl_user.lastname, dbo.mdl_course.shortname, dbo.mdl_course.fullname, DATEADD(S, dbo.mdl_course_completions.timeenrolled, '1970-01-01') AS EnrolledDate, DATEADD(S,
dbo.mdl_course_completions.timestarted, '1970-01-01') AS StartedDate, DATEADD(S, dbo.mdl_course_completions.timecompleted, '1970-01-01') AS CompletedDate
FROM dbo.mdl_user_info_data RIGHT OUTER JOIN
dbo.mdl_user ON dbo.mdl_user_info_data.userid = dbo.mdl_user.id RIGHT OUTER JOIN
dbo.mdl_course RIGHT OUTER JOIN
dbo.mdl_course_completions ON dbo.mdl_course.id = dbo.mdl_course_completions.course ON dbo.mdl_user.id = dbo.mdl_course_completions.userid
WHERE (dbo.mdl_course_completions.timecompleted IS NOT NULL) AND (dbo.mdl_user_info_data.fieldid = 1)
SELECT dbo.mdl_user.username, dbo.mdl_user_info_data.data AS EmployeeNum, dbo.mdl_user.firstname, dbo.mdl_user.lastname, dbo.mdl_course.shortname, dbo.mdl_course.fullname, DATEADD(S, dbo.mdl_course_completions.timeenrolled, '1970-01-01') AS EnrolledDate, DATEADD(S,
dbo.mdl_course_completions.timestarted, '1970-01-01') AS StartedDate, DATEADD(S, dbo.mdl_course_completions.timecompleted, '1970-01-01') AS CompletedDate
FROM dbo.mdl_user_info_data RIGHT OUTER JOIN
dbo.mdl_user ON dbo.mdl_user_info_data.userid = dbo.mdl_user.id RIGHT OUTER JOIN
dbo.mdl_course RIGHT OUTER JOIN
dbo.mdl_course_completions ON dbo.mdl_course.id = dbo.mdl_course_completions.course ON dbo.mdl_user.id = dbo.mdl_course_completions.userid
WHERE (dbo.mdl_course_completions.timecompleted IS NOT NULL) AND (dbo.mdl_user_info_data.fieldid = 1)