Computers Shipped by Year
I am trying to get a report that will tell me the number of computers shipped per previous years based solely on the Dell Warranty "Ship Date" data. I started with the template report "Dell Warranty Expired" since the desired columns are there (name and ship date). The report works and is sorted by Ship Date but it includes many duplicate asset records instead of just active computer records. Is there a better way to do this (other than deleting the duplicates)?
Also, I can do a Break on Columns for the grouping of similar dates but ideally it would just count the number of records by year:
Also, I can do a Break on Columns for the grouping of similar dates but ideally it would just count the number of records by year:
SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE, DW.END_DATE AS WARRANTY_EXPIRATION_DATE
FROM KBSYS.DELL_WARRANTY DW
LEFT JOIN KBSYS.DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
LEFT JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!='
AND DA.DISABLED != 1
order by DA.SHIP_DATE
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
I found an answer by modifying a different post (http://beta.itninja.com/question/installation-workflow-and-tools04). Thanks dchristian and you get some points!: SELECT M.NAME, Â Â Â Â DA.SERVICE_TAG, Â Â Â Â DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d')AS SHIP_DATE, Â Â Â Â M.IP Â FROM KBSYS.DELL_ASSET DA, Â Â Â MACHINE M WHERE DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER ORDER BY DA.SHIP_DATE
I found an answer by modifying a different post (http://beta.itninja.com/question/installation-workflow-and-tools04). Thanks dchristian and you get some points!: SELECT M.NAME, Â Â Â Â DA.SERVICE_TAG, Â Â Â Â DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d')AS SHIP_DATE, Â Â Â Â M.IP Â FROM KBSYS.DELL_ASSET DA, Â Â Â MACHINE M WHERE DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER ORDER BY DA.SHIP_DATE
Please log in to answer
Posted by:
RichB
12 years ago
I found an answer by modifying a different post (http://itninja.com/question/installation-workflow-and-tools04). Thanks dchristian and you get some points!:
SELECT M.NAME,
DA.SERVICE_TAG,
DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d')AS SHIP_DATE,
M.IP
FROM KBSYS.DELL_ASSET DA,
MACHINE M
WHERE DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER
ORDER BY DA.SHIP_DATE
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.