Using relative date when creating derived table

0

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

drew_1's picture
11
Joined: Nov 13 2015 - 12:14pm
Last seen: 2 years 1 week ago

0 Answers:

No answers yet
Feedback