Receiving double responses in Warranty Report
I have a bit of code here that should return everything in Kace's sytem with an expired warranty. And yet, I receive a lot of double results.
For example, from the same machine (name, model, Service tag) I've received in the same report three entries for:
Here I see two doubles, but they shouldnt even be there if it was renewed, right? Is there any way to clean this up? I'm at a loss.
Here's the code. Thanks in advance!
SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.SERVICE_LEVEL_CODE, DW.SERVICE_LEVEL_DESCRIPTION, DW.END_DATE AS 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 DA.DISABLED != 1
AND DW.END_DATE < NOW()
AND DW.SERVICE_TAG NOT IN ( SELECT SERVICE_TAG FROM KBSYS.DELL_WARRANTY WHERE END_DATE > NOW());
See here: http://www.itninja.com/question/dell-expired-warranty-report-multiple-listing-for-each-machine-need-unique-only