SELECT
    rh.RevisionHardwareID AS 'Machine_Information_Id__c',
    COALESCE(lmi.Name, lmz.Name) as 'License_Method__c',
    rhd.Servers AS 'Servers__c',
    rhd.Managers AS 'Managers__c',
    COALESCE(rhd.LicenseCount, rhs.CPUCount, rhz.MIPS, rhi.CPW) AS 'Quantity__c',
    CASE
        WHEN rhd.RevisionHardwareID IS NOT NULL THEN '0122J00000023maQAA'
        WHEN rhi.RevisionHardwareID IS NOT NULL THEN '0122J00000023mcQAA'
        WHEN rhz.RevisionHardwareID IS NOT NULL THEN '0122J00000023mdQAA'
        WHEN rhs.RevisionHardwareID IS NOT NULL THEN '0122J00000023mbQAA'
    END AS 'RecordTypeId'
FROM RevisionHardware rh
LEFT JOIN RevisionHardware_Desktop rhd ON rhd.RevisionHardwareID = rh.RevisionHardwareID
LEFT JOIN RevisionHardware_iSeries rhi ON rhi.RevisionHardwareID = rh.RevisionHardwareID
LEFT JOIN RevisionHardware_zSeries rhz ON rhz.RevisionHardwareID = rh.RevisionHardwareID
LEFT JOIN RevisionHardware_Server rhs ON rhs.RevisionHardwareID = rh.RevisionHardwareID
LEFT JOIN LicenseMethod lmi ON lmi.LicenseMethodID = rhi.LicenseMethod_ISeriesID
LEFT JOIN LicenseMethod lmz ON lmz.LicenseMethodID = rhz.LicenseMethod_ZSeriesID
WHERE CASE
        WHEN rhd.RevisionHardwareID IS NOT NULL THEN 'Desktop'
        WHEN rhi.RevisionHardwareID IS NOT NULL THEN 'iSeries'
        WHEN rhz.RevisionHardwareID IS NOT NULL THEN 'zSeries'
        WHEN rhs.RevisionHardwareID IS NOT NULL THEN 'Server'
    END IS NOT NULL