/build/static/layout/Breadcrumb_cap_w.png

Best Practices Question


SQL report: show computers with missing information

07/02/2020 116 views

Hi everyone

We use following report to gather information about all computers. It has occurred that some computers don't show up in the report due to missing information from the Service Tag by DELL.
We'd like to have all computers shown even though some information might be missing.

Can anybody help us to improve that in the SQL quiery?
Thank you.


Select
  mach.ID As Id,
  Asset_Firma.NAME As Firma,
  mach.NAME As Computer,
  mach.CS_MANUFACTURER As Hersteller,
  mach.CS_MODEL As Modell,
  Asset_Standort.NAME As Standort,
  Asset_Raum.NAME As Raum,
  ORG1.ASSET_DATA_5.FIELD_10038 As Kontaktname,
  Asset_KST.NAME As KST,
  ORG1.ASSET_DATA_5.FIELD_10037 As Bemerkung,
  ORG1.ASSET_DATA_5.FIELD_10040 As "IT Information",
  mach.BIOS_SERIAL_NUMBER As Servicetag,
  xtern.DESCR As Servicelevel,
  xtern.ENDD As Enddatum,
  Case When xtern.ENDD > Now() Then 'Ja' Else 'Nein' End As Service,
  Case When xtern.ENDD < Now() Then 0 Else DateDiff(xtern.ENDD, Now())
  End As Tage
From
  ORG1.MACHINE mach Left Join
  ORG1.ASSET ast
    On ast.MAPPED_ID = mach.ID Inner Join
  ORG1.ASSET_ASSOCIATION Assoc_Standort
    On Assoc_Standort.ASSET_ID = ast.ID Inner Join
  ORG1.ASSET Asset_Standort
    On Asset_Standort.ID = Assoc_Standort.ASSOCIATED_ASSET_ID Inner Join
  ORG1.ASSET_ASSOCIATION Assoc_Firma
    On Assoc_Firma.ASSET_ID = ast.ID Inner Join
  ORG1.ASSET Asset_Firma
    On Asset_Firma.ID = Assoc_Firma.ASSOCIATED_ASSET_ID Inner Join
  ORG1.ASSET_ASSOCIATION Assoc_KST
    On Assoc_KST.ASSET_ID = ast.ID Inner Join
  ORG1.ASSET Asset_KST
    On Asset_KST.ID = Assoc_KST.ASSOCIATED_ASSET_ID Inner Join
  ORG1.ASSET_ASSOCIATION Assoc_Raum
    On Assoc_Raum.ASSET_ID = ast.ID Inner Join
  ORG1.ASSET Asset_Raum
    On Asset_Raum.ID = Assoc_Raum.ASSOCIATED_ASSET_ID Inner Join
  ORG1.ASSET_DATA_5
    On ORG1.ASSET_DATA_5.ID = ast.ASSET_DATA_ID,
  (Select
    dellw.SERVICE_TAG As ST,
    Max(dellw.END_DATE) As ENDD,
    dellw.SERVICE_LEVEL_DESCRIPTION As DESCR
  From
    ORG1.DELL_WARRANTY dellw
  Where
    dellw.SERVICE_LEVEL_GROUP = '5'
  Group By
    dellw.SERVICE_TAG) As xtern
Where
  xtern.ST = mach.BIOS_SERIAL_NUMBER And
  ast.ASSET_TYPE_ID = 5 And
  Asset_Firma.NAME = 'SGV AG' And
  Asset_Standort.ASSET_TYPE_ID = 1 And
  Asset_Firma.ASSET_TYPE_ID = 2 And
  Asset_KST.ASSET_TYPE_ID = 3 And
  Asset_Raum.ASSET_TYPE_ID = 15
Order By
  Firma,
  Computer


0 Comments   [ + ] Show comments

Comments


All Answers

0

You have a full join from MACH (MACHINE) to your xtern subtable


Answered 07/02/2020 by: JordanNolan
8th Degree Black Belt

  • Thank you for your answer. What does that mean and what need to be changed or deleted?
0

Change all of your inner join statements to left join statements.

Answered 07/06/2020 by: chucksteel
Red Belt

  • Thanks for the solution I was facing the issue
 
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