Using relative date when creating derived table

Jaspersoft Community 'Read-Only' as of July 8, 2022

Transition to New TIBCO Community Just Weeks Away

You can still search, review wiki content, and review discussions in read-only mode. Please email community@tibco.com with questions or issues requiring TIBCO review or response.

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
118
Joined: Nov 13 2015 - 12:14pm
Last seen: 1 month 2 weeks ago

0 Answers:

No answers yet
Feedback
randomness