Help getting label information to print on Dell End of Life Report
I have tried creating this report and testing in SQL workbench but can't seem to get either the warranty information to not error out or printing labels. I can separate into two different reports and make them work.
Anyone help with what is incorrect?
SELECT DISTINCT(MACHINE.NAME),
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
MACHINE.CHASSIS_TYPE,
(SELECT group_concat(distinct if(LABEL.NAME not like 'HDN_LABEL_%', LABEL.NAME, 'System Hidden') separator '\n')
USER_FULLNAME,
DA.SHIP_DATE AS "Ship Date",
DATE_ADD(DA.SHIP_DATE, INTERVAL 4 YEAR) as "Replacement Date"
MAX(DW.END_DATE) AS "Warranty End Date",
FROM MACHINE_LABEL_JT MLJT INNER JOIN LABEL ON MLJT.LABEL_ID = LABEL.ID
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID)
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)
WHERE MACHINE.ID = MLJT.MACHINE_ID ORDER BY LABEL.NAME)as LABEL_NAME FROM MACHINE
WHERE (((not exists (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden'
and LABEL.NAME like '%Machines - Recycled%')) )
AND ((not exists (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden'
and LABEL.NAME like '%Stored in IT%')) ))
GROUP BY MACHINE.NAME
Answers (1)
Top Answer
I'd break out the warranty information to a sub-select and then join that to the machine/label info. Here's a simplified version of that method:
SELECT
MACHINE.NAME,
GROUP_CONCAT(LABEL.NAME) AS 'All Labels',
WARRANTY.SHIP,
WARRANTY.REPLACE,
WARRANTY.END
FROM
MACHINE
JOIN
MACHINE_LABEL_JT ML ON ML.MACHINE_ID = MACHINE.ID
JOIN
LABEL ON ML.LABEL_ID = LABEL.ID
LEFT JOIN
(SELECT
MACHINE.ID,
DA.SHIP_DATE AS 'SHIP',
DATE_ADD(DA.SHIP_DATE, INTERVAL 4 YEAR) AS 'REPLACE',
MAX(DW.END_DATE) AS 'END'
FROM
MACHINE
LEFT JOIN DELL_ASSET DA ON MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW ON MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
GROUP BY MACHINE.ID) WARRANTY
ON WARRANTY.ID = MACHINE.ID
GROUP BY MACHINE.ID
Comments:
-
Thank you I guess I was trying to make it more complicated than it needed to be. - scarpent 4 years ago
