Here is the query that is supposed to give me results for the last 12 months, but it is giving me the whole results since 2014.
What is wrong with it?
I tried doing
(where a.time > '2020-01-01 00:00:00')
but I do not want a fixed date in the query.
SET @no_of_months = 12; #change to the desired no. of months
CREATE TEMPORARY TABLE if NOT EXISTS newAuditlog
SELECT
a.id, a.user, a.action, a.project, a.info, a.TIME, a.project_ref, a.workpackage_ref, a.partner_ref
FROM auditlog a
WHERE a.time < (DATE_SUB(CURDATE(), INTERVAL @no_of_months MONTH))
GROUP BY user
ORDER BY user;
SELECT
na.id, na.user, b.roles, na.action, na.project, na.info, na.TIME, na.project_ref, na.workpackage_ref, na.partner_ref
FROM newAuditlog na
INNER JOIN(
SELECT u.id AS user_id,
u.username,
GROUP_CONCAT(r.role ORDER BY r.role SEPARATOR ', ') AS roles
FROM user_has_role uhr
LEFT OUTER JOIN user u ON (uhr.user_id = u.id)
LEFT OUTER JOIN role r ON (uhr.role_id = r.id)
GROUP BY uhr.user_id
ORDER BY uhr.user_id
)b
ON SUBSTR(na.user, INSTR(na.user, "(")+1, INSTR(na.user, ")") -1- (INSTR(na.user, "("))) = b.user_id;
DROP TEMPORARY TABLE newAuditlog;
question from:
https://stackoverflow.com/questions/66064530/my-query-isnt-working-properly-having-time-issue-with-the-query-itself 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…