Summary Query - I am stymied by two issues.

Summary Query - I am stymied by two issues.

by Shirley Gregorczyk -
Number of replies: 6
Picture of Particularly helpful Moodlers

I have cobbled together a summary query to help me quickly look for students that need some encouragement to complete their assigned courses.

I am stymied by two issues.

1. My Sum statement does not product an error, but it also does not produce any results. My workaround is to export to Excel and do the math there, but I would like to learn how to correctly create a SUM statement.

2. My SQL Query is off-here and there because when an active employee is moved to a new job position their cohort membership is removed. The filter current enrolments is not reading the adjusted cohort membership.
The course participation's enrollments are correct, but my SQL Queries are always a little overstated here and there.

Is there a way I can correct the issues I am having with this query?

SELECT DISTINCT
CONCAT(u.firstname, ' ', u.lastname) AS Name,
COUNT(p.timeenrolled) as 'Assigned',
COUNT(p.timecompleted) AS 'TotalCompletions',
SUM(('TotalCompletions' / 'Assigned' )*100) As PercentComplete
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id AND c.visible = 1
JOIN prefix_user AS u ON p.userid = u.id

AND u.deleted = 0 AND u.suspended = 0
%%FILTER_COURSES:id%%
%%FILTER_COURSEENROLLEDSTUDENTS%%
GROUP BY p.userid
ORDER BY Name


Kind Regards

Average of ratings: -
In reply to Shirley Gregorczyk

Re: Summary Query - I am stymied by two issues.

by Randy Thornton -
Picture of Documentation writers
Shirley,

For the SUM just use the actual columns in the calculation: p.timecompleted / p.timeenrolled.

For the cohorts, is the user being re-enrolled again in the same course via another cohort? That is, unenrolled and then re-enrolled? And if so, then the user is probably taken out of this course completion table on unenroll and being added back again on the new enrol with its new dates.
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Summary Query - I am stymied by two issues.

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
Hi Randy,
Thank you for the Sum information.

The cohort situation is a mix-bag. Some students are enrolled into the same course with another cohort. Some are assigned new cohorts and courses. The employee moved to another department and/or supervisory role. I evaluated that 46% of my query is spot-on! The remainder has job changes, or course revisions and or the course audience changed since the start date of the course.
Average of ratings: -
In reply to Shirley Gregorczyk

Re: Summary Query - I am stymied by two issues.

by Randy Thornton -
Picture of Documentation writers
Shirley,

I hear you. I don't think there is really anyway around this issue if users are being unenrolled and reenrolled so that the enrollment data is changing. Unless you saved the old original date in some report somewhere. Especially if they are moving from job to job, course to course.

Could you get around this completion issue with cross course competencies? Maybe some made up ones that mimic the course completions?
Average of ratings: -
In reply to Randy Thornton

Re: Summary Query - I am stymied by two issues.

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
Hello Randy,
Wonderful suggestion- I will have to noodle on that.
Kind Regards,
Shirley
Average of ratings: -
In reply to Shirley Gregorczyk

Re: Summary Query - I am stymied by two issues.

by Randy Thornton -
Picture of Documentation writers
Yeah, it just struck me as interesting if you had the same competency at the start of several courses that was fulfilled right away by just clicking or reading something. That would then capture the time they first started work on the same "program" even if there are several entry points across courses for that. So, moving enrollments around would not change that first datetime.
Average of ratings: -