Limit my report to KB articles older than X amount of days (SQL)
Hello KBOX warriors,
I have a little custom report I've made and it works a treat but I just can't get the last piece to work. This report is very basic, it just shows KB article #, subject, the created & modified dates, and how many days old. The only piece I can't get working is to filter it so it only shows reports over X amount of days old. The end goal is to only see reports over a year old which I would express as >= 365 days. SQL Geniuses, this will probably be easy-peasy for you folks. Thanks in advance for your sage advice
select CONCAT('KB:0000',ADVISORY.ID) as 'Article ID', ADVISORY.TITLE as Title, ADVISORY.CREATED as Created, ADVISORY.MODIFIED as Modified, DATEDIFF(CURDATE(), ADVISORY.CREATED) AS 'Days Old' from ADVISORY
left join ADVISORY_LABEL_JT on ADVISORY_LABEL_JT.ADVISORY_ID = ADVISORY.ID
left join LABEL on LABEL.ID = ADVISORY_LABEL_JT.LABEL_ID
where (1 = 1)
group by ADVISORY.CREATED order by CREATED ASC
Answers (1)
Top Answer
I got it worked out and cleaned up some stuff from the auto-generated report that wasn't necessary. My final result is...
select CONCAT('KB:0000',ADVISORY.ID) as 'Article ID', ADVISORY.TITLE as Title, ADVISORY.CREATED as Created, ADVISORY.MODIFIED as Modified, DATEDIFF(CURDATE(),ADVISORY.CREATED) AS 'Days Old' from ADVISORY WHERE DATEDIFF(CURDATE(),ADVISORY.CREATED) > 80
and it works as intended.