Multiple course completions that outputs single row for student

Multiple course completions that outputs single row for student

by Richard French -
Number of replies: 2

Hello. I'm hoping that someone has done something like this before and can point me in the right direction. For background, we use a course naming scheme that includes a revision level in the course shortname. For example, the "grape" course is the original course. "grape1" is the first update, "grape2" is the second update, and so on.

For our reporting, it doesn't matter which "grape" course the student completed, but they must have completed at least one. But if they've completed more than one, I have to report just the latest completion date of all the "grape" courses they've completed. I thought I could take advantage of the course naming by using "grape%", but I get lost quickly from there. 

Complicating matters (at least for me) is that we have many "fruit" classes that comprise a track of courses that require completion reporting. For example, a rough pseudocode for this reporting would be:

  • If (student has completed any "apple" course, get its latest completion date) AND (if student has completed any "grape" course, get its latest completion date) AND (if student has completed any "pear" course, get its latest completion date) 
  • Then (print MAX(course completion dates from each of the "fruit" courses)) and output "Fruit track completed". 

Only this one date can be shown on any reports. Any and all suggestions are appreciated,

Richard


Average of ratings: -
In reply to Richard French

Re: Multiple course completions that outputs single row for student

by Luis de Vasconcelos -
Have you written any sql code to try do this yet? If so please share that code.

Are you grouping the apple, grade and pears courses in some way, e.g. in course categories?
Average of ratings: -
In reply to Luis de Vasconcelos

Re: Multiple course completions that outputs single row for student

by Richard French -
The following is the data and what I have so far. The course data is:
- Apple course(s): Apple and Apple1
- Grape course(s): Grape
- Pear course(s): Pear, Pear1, and Pear2
As a reminder, student must complete at least one of each to complete the track. The following is how it looks now (i.e. does NOT work):

SELECT concat(u.firstname, ' / ', u.lastname) as "username",

u.email as "email address",

#
# Course completion (latest date)
#
DATE_FORMAT(FROM_UNIXTIME(MAX(p.timecompleted)), '%m%d%Y') AS "effective date",

"All fruits completed" as "track completed"

FROM prefix_course_completions AS p
LEFT OUTER JOIN prefix_course AS c ON (c.id = p.course)
LEFT OUTER JOIN prefix_user AS u ON (p.userid = u.id)

WHERE(u.confirmed = 1) AND (u.deleted = 0) AND (u.suspended = 0) AND ((c.shortname LIKE 'Apple%') OR (c.shortname LIKE 'Grape%') OR (c.shortname LIKE 'Pear%'))

GROUP BY u.id

HAVING COUNT(p.timecompleted) = 3

ORDER BY MAX(p.timecompleted) DESC
Average of ratings: -