www.blackholesurfer.com

Code: Select all

SELECT 
  COUNT(*) AS record_count,
  CASE 
    WHEN b.normalizationstatus = '10' THEN 'Other'
    WHEN b.normalizationstatus = '20' THEN 'Not Normalized'
    WHEN b.normalizationstatus = '30' THEN 'Not Applicable for Normalization'
    WHEN b.normalizationstatus = '40' THEN 'Normalization Failed'
    WHEN b.normalizationstatus = '50' THEN 'Normalized but Not Approved'
    WHEN b.normalizationstatus = '60' THEN 'Normalized and Approved'
    WHEN b.normalizationstatus = '70' THEN 'Modified after last Normalization'
  END AS NormalizationStatus,
  b.Classid,
  b.Category,
  b.Type,
  b.Item,
  b.Model,
  COALESCE(b.ManufacturerName, 'BMC_UNKNOWN') AS ManufacturerName,
  CASE 
    WHEN b.Company = 'ACME Co' THEN '- Global -'
    ELSE b.Company
  END AS Company,
  c.Description AS COM_Company,        
  c.Company_Type AS CompanyType
FROM BMC_CORE_BMC_BaseElement b
LEFT JOIN COM_Company c
  ON c.Company_Type LIKE '%Manufacturer%'
  AND b.ManufacturerName = c.Description
WHERE b.datasetid = 'BMC.ADDM'
  AND b.NormalizationStatus = '40'
  AND b.Model IS NOT NULL
  AND (c.Description IS NULL OR b.ManufacturerName <> c.description)   -- ✅ mismatch or no match
GROUP BY 
  NormalizationStatus, 
  b.Classid,
  b.Category,
  b.Type,
  b.Item,
  b.Model,
  b.ManufacturerName,
  b.Company,
  c.Company,
  c.Company_type,
  c.Description;