I want to make a report that shows students who have logged in during the last week. I'm using "Customizable reports" plugin to do that and went with editing one of the default report presets:
SELECT u.firstname, u.lastname, h.name, FROM_UNIXTIME( lastlogin ) AS days
FROM prefix_cohort AS h
JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
JOIN prefix_role_assignments AS ra
JOIN prefix_user AS u ON hm.userid = u.id WHERE DATEDIFF(NOW(),FROM_UNIXTIME( lastlogin ) ) < 7 AND ra.roleid = 5
ORDER BY h.name
I don't know much about SQL as you could probably tell, so this solution I came up with does not work as intended. Namely, I'm getting 5000 rows filled with the same student, and nothing else.
Could someone help me with this?
Also, is it possible to color\highlight specific users based on the WHERE clause? I googled some solutions for that but they seem to be specific and too hard to read for me. I'd like to make another report that instead of showing students who have logged in week ago, shows everyone and highlights the students that have logged in during the last week.