/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: JasonEgg 3 years ago
Red Belt
0

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 3 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ