SELECT DISTINCT
vRM_Computer_Item.Name, Inv_HW_Logical_Device.Manufacturer, Inv_HW_Logical_Device.Model, Inv_AeX_AC_Identification.[OS Name],
Inv_AeX_AC_Primary_User.[User], ISNULL(CAST(CAST(Inv_HW_Storage.[Max Media Size (Kilobytes)] / (1024 * 1024) AS DECIMAL(10, 0)) AS NVARCHAR(MAX)), '--')
AS [HDD Size (GB)], Inv_SW_BIOS_Element.[Release Date] AS [BIOS Release Date],
ISNULL(CAST(CAST(vHWComputerSystem.[Total Physical Memory (Bytes)] / (1024.0 * 1024.0) AS DECIMAL(10, 0)) AS NVARCHAR(MAX)), '--') AS [RAM (MB)],
derivedtbl_1.[Memory Devices]AS [Total Number of Slots], temp.[Used Slots]
FROM vRM_Computer_Item INNER JOIN
Inv_HW_Logical_Device ON vRM_Computer_Item.Guid = Inv_HW_Logical_Device._ResourceGuid INNER JOIN
Inv_AeX_AC_Identification ON vRM_Computer_Item.Guid = Inv_AeX_AC_Identification._ResourceGuid INNER JOIN
Inv_AeX_AC_Primary_User ON vRM_Computer_Item.Guid = Inv_AeX_AC_Primary_User._ResourceGuid INNER JOIN
Inv_HW_Storage ON vRM_Computer_Item.Guid = Inv_HW_Storage._ResourceGuid INNER JOIN
Inv_SW_BIOS_Element ON vRM_Computer_Item.Guid = Inv_SW_BIOS_Element._ResourceGuid INNER JOIN
vHWComputerSystem ON vRM_Computer_Item.Guid = vHWComputerSystem._ResourceGuid LEFT OUTER JOIN
(SELECT SUM([Memory Devices]) AS [Memory Devices], _ResourceGuid
FROM vHWPhysicalMemoryArray
WHERE ([Use] = 3)
GROUP BY _ResourceGuid) AS derivedtbl_1 ON derivedtbl_1._ResourceGuid = vHWComputerSystem._ResourceGuid LEFT OUTER JOIN
(SELECT COUNT(DISTINCT Inv_HW_Physical_Memory_Location.[Part Component]) AS [Used Slots], vHWPhysicalMemory._ResourceGuid
FROM vHWPhysicalMemory INNER JOIN
Inv_HW_Physical_Memory_Location ON vHWPhysicalMemory._ResourceGuid = Inv_HW_Physical_Memory_Location._ResourceGuid AND
vHWPhysicalMemory.[Device ID] = Inv_HW_Physical_Memory_Location.[Part Component] INNER JOIN
vHWPhysicalMemoryArray AS vHWPhysicalMemoryArray_1 ON
vHWPhysicalMemoryArray_1._ResourceGuid = Inv_HW_Physical_Memory_Location._ResourceGuid AND
vHWPhysicalMemoryArray_1.Tag = Inv_HW_Physical_Memory_Location.[Group Component]
WHERE (vHWPhysicalMemoryArray_1.[Use] = 3)
GROUP BY vHWPhysicalMemory._ResourceGuid) AS temp ON temp._ResourceGuid = vHWComputerSystem._ResourceGuid
WHERE (vRM_Computer_Item.Guid IN
(SELECT DISTINCT vRM_Computer_Item_1.Guid
FROM vRM_Computer_Item AS vRM_Computer_Item_1 INNER JOIN
Inv_HW_Logical_Device AS Inv_HW_Logical_Device_1 ON vRM_Computer_Item_1.Guid = Inv_HW_Logical_Device_1._ResourceGuid
WHERE (Inv_HW_Logical_Device_1.Description LIKE '%battery%'))) AND (Inv_HW_Logical_Device.Description LIKE 'AT/AT COMPATIBLE') AND
(Inv_HW_Storage.[Media Type] LIKE '29') AND (Inv_HW_Storage.[Interface Type] NOT LIKE 'USB%') AND (Inv_HW_Storage.[Max Media Size (Kilobytes)] > 200000)