Hi Folks,
I'm having a problem getting a relative date to work when trying to create a derived table. We are utilizing some of the monitoring data to see when users have accessed reports. To make sure the usage is somewhat current, I wanted to see any user that hasn't accessed a report in say, two months time. I came up with this derived table becasue the built in join functions wouldn't really allow me to filter out the positives (and leave just the null results). This should give me anyone who has not accessed a report ever, or has not accessed a report in a long time. The compiler gets mad when I add the date_sub function. I haven't found anything in the DomEL Syntax docs to suggest a way to do relative dating.
SELECT jiu.id AS user_id, jiu.fullname AS fullname, jiu.username AS username, jia.event_date, jia.id AS access_id FROM jiuser jiu LEFT JOIN jiaccessevent jia ON jia.user_id = jiu.id WHERE (jia.id IS NULL OR jia.event_date <= DATE_SUB(NOW() - INTERVAL 60 DAY))
I appreciate any thoughts or suggestions anyone has.
Thanks,
Drew
