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:

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
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
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
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
Answered 02/28/2012 by: RichB
Third Degree Green Belt

Please log in to comment
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Thanks!!!!
Answered 02/28/2012 by: dchristian
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity