Return only unique machines w/ expired warranty
One of the built-in reports in the K1000 is a report of Dell machines with expired warranties. This is a great report, except that all of our machines have multiple warranties applied (Complete Care + Keep your hard drive, etc). This results in double the number of machines in the listing, since each computer is listed for every warranty contract that has expired. Has anyone tackled this problem and written a report that will return a single listing for each computer?
SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE, GROUP_CONCAT(DISTINCT DW.SERVICE_LEVEL_CODE SEPARATOR '\n') AS Service_Level_Code, GROUP_CONCAT(DISTINCT DW.SERVICE_LEVEL_DESCRIPTION SEPARATOR '\n') AS Service_Level_Desc, GROUP_CONCAT(DISTINCT DW.END_DATE SEPARATOR '\n') 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 DW.END_DATE < NOW() GROUP BY M.NAME