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:
Please log in to answer
Posted by:
chucksteel
7 years ago
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