/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Setting the Interval in a report

02/13/2017 587 views
I have the following script to list the PCs in Kace older than 3 years.

SELECT MACHINE.NAME, CS_MODEL, BIOS_SERIAL_NUMBER, DA.SHIP_DATE, USER_FULLNAME, USER_LOGGED,  MACHINE.OS_NAME
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG

WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
HAVING DA.SHIP_DATE < DATE_SUB(NOW(), INTERVAL 3 YEAR)
ORDER BY DA.SHIP_DATE, MACHINE.NAME

My question is this: how do you manipulate the 'Interval' so that, for instance, I could list PCs greater than 3 years old but less than 5? Is this possible?

Thanks!
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
Yes, use date between:
DA.SHIP_DATE BETWEEN DATE_SUB(NOW(), INTERVAL 5 YEAR) and DATE_SUB(NOW(), INTERVAL 3 YEAR)

Here is a good reference:
Answered 02/14/2017 by: chucksteel
Red Belt

  • Sorry for taking so long to get back to you - I only just got the chance to test this! It works perfectly, thanks! The only thing I had to do was add HAVING at the start of the line as I got an SQL error without it. Thanks again!

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