i have a scenario where i have two groups of users inside a course:, one group have attempted the quize inside the course, and therefore their quiz completion date is recorded automatically on the db, so i am able to call this value on my report. The other group; i have manually graded their grades, and i created an additional profile field called 'CompletionDate' and then manually added the values on this field.
On the code below, i have a column on my report which i named 'completion date'. on this i want to display two different values, which is quiz completion date and the value stored inside the additional field 'CompletionDate'; depending on whether there is a value on either fields (quiz completion or 'CompletionDate). so this means, if the value inside the 'CompletionDate' field is empty; this means the user has attempted the quiz, thus the code should return the quiz completion date (gg.timecreated); if the there is a value inside the 'CompletionDate' field then the code should return the value stored.
below is the code i'm using; somehow it returns only the users who have values stored inside the 'CompletionDate' and omits users who have attempted the quiz. is there something wrong with my code?
SELECT u.username AS Code,
u.firstname AS Name,
u.lastname AS Surname,
c.fullname AS Assessment,
c.id AS 'Quiz ID',
q.intro AS 'Product code',
u.yahoo AS Province,
u.msn AS Unit,
ROUND(gg.finalgrade, 0)as grade,
if(d.data != " ",d.data,FROM_UNIXTIME(gg.timecreated, '%Y-%m-%d')) as 'completion date',
FROM prefix_quiz q
JOIN prefix_course c ON c.id = q.course
JOIN prefix_grade_items gi ON gi.iteminstance = q.id
JOIN prefix_grade_grades gg ON gg.itemid = gi.id
JOIN prefix_user u ON u.id = gg.userid
join prefix_user_info_field f
left join prefix_user_info_data d
on f.id = d.fieldid
and f.shortname = 'CompletionDate'
WHERE (c.id = 1372)
and (d.fieldid = f.id AND d.userid=u.id)
AND gg.finalgrade !=" "
Group By gg.id