How to validate higher value received in mysql?

Asked

Viewed 225 times

0

I have a query that returns the last accesses of the users in the system. I would like to know how I could treat to return only those dates that are longer than 15 days.

This is the consultation:

SELECT u.id, concat(u.firstname,' ',u.lastname) as nome,from_unixtime(MAX(l.time)),
DATEDIFF(now(),from_unixtime(MAX(l.time))) as Dias
FROM mdl_role_assignments rs
    INNER JOIN mdl_user u ON u.id=rs.userid
    INNER JOIN mdl_context e ON rs.contextid=e.id
    INNER JOIN mdl_log l ON l.userid=u.id
WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=2174 AND l.course=e.instanceid
GROUP BY u.id, u.firstname,u.lastname
order by Dias desc

The main table for this operation is the mdl_log which stores these records with iduser and Course (which I used to filter the course id=2174 in e. instanceid)

I already tried to pass the function MAX() in Where, I tried to pass that same SELECT in WHERE comparing the return with any date... without success.

mysql sql select moodle

  • if you only want the last 15 days, it would not be enough to just compare the date using (CURDATE() - INTERVAL 15 DAY)?

  • The problem is that the table keeps all access dates, why I passed the l.time field within max()

  • Remembering that if I have to catch the last access and that it has been more than 15 days

1 answer

1

Try the comparison on WHERE as directed by @Ricardo Pontual

SELECT u.id, concat(u.firstname,' ',u.lastname) as nome, from_unixtime(MAX(l.time)),
DATEDIFF(now(),from_unixtime(MAX(l.time))) as Dias
FROM mdl_role_assignments rs
    INNER JOIN mdl_user u ON u.id=rs.userid
    INNER JOIN mdl_context e ON rs.contextid=e.id
    INNER JOIN mdl_log l ON l.userid=u.id
WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=2174 AND l.course=e.instanceid AND from_unixtime(l.time) > (CURDATE() - INTERVAL 15 DAY)
GROUP BY u.id, u.firstname,u.lastname
order by Dias desc

In this way all the records of the last 15 days will be filtered and then, if there is, the last of these dates will be presented. If to return ALL dates longer than 15 days (more than one record) remove GROUP BY and MAX.

Browser other questions tagged mysql sql select moodle

You are not signed in. Login or sign up in order to post.