/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 252 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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ