General developer forum
Useful SQL Queries?
I've been given a challenging request for a highly customized course completions report and I'm hoping that someone has done something similar. 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 (student has completed any "grape" course, get its latest completion date) AND (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,