Create Report for machines five years and older from Ship Date.
09/15/2014 1935 views
I have tried and searched but I cannot figure out how to create a report that takes the Ship Date and then reports back all items that are five years and older and which ones are getting close to five years. Does anyone have something like this?
Answer Chosen by the Author
Please log in to answer
This query will find Dell computers over five years old:
SELECT MACHINE.NAME, MACHINE.OS_NAME, MACHINE.CS_MANUFACTURER, DA.SHIP_DATE
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 5 YEAR)
ORDER BY DA.SHIP_DATE, MACHINE.NAME
I'm not sure how you would define "close to five years" but you can play with the interval clause to get different results.
Answered 09/16/2014 by: chucksteel