/build/static/layout/Breadcrumb_cap_w.png

Setting the Interval in a report

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!

0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 7 years ago
Red Belt
1

Top Answer

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:

Comments:
  • 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! - seanboy 7 years ago

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