/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Limit my report to KB articles older than X amount of days (SQL)

05/30/2019 202 views

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

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1

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. 

Answered 05/30/2019 by: pfletcher
Yellow Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share