Hello,
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?

Thanks.
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

2
This query will find Dell computers over five years old:
SELECT MACHINE.NAME, MACHINE.OS_NAME, MACHINE.CS_MANUFACTURER, DA.SHIP_DATE
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 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
Red Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity