SELECT 
	cc.ComplianceComputerID,
	cc.ComputerName,
	ccs.DefaultValue as ComputerStatus,
	cc.ArchivedDate,
	a.AssetID,
	a.ShortDescription as Asset_ShortDescription,
	ast.StatusDefaultValue as AssetStatus,
	ibmTitles.SoftwareTitleID,
	st.FullName as Title,
	tins.ValidFrom as InstallValidFrom,
	tins.ValidTo as InstallValidTo,
	tins.SoftwareLicenseID as AssignedSoftwareLicenseID, -- will allways be null due to the bug
	ibmTitles.LicenseIDs,
	ibmTitles.Licenses
FROM
	dbo.ComplianceComputer as cc
	JOIN dbo.Asset as a  ON cc.AssetID = a.AssetID AND a.AssetStatusID IN (2,4,5) -- retired, disposed OR in storage
	JOIN dbo.TemporalInstalledSoftwareSCD as tins ON tins.ComplianceComputerID = cc.ComplianceComputerID
	JOIN (
		SELECT 
			stl.SoftwareTitleID,
			STRING_AGG(sl.Name, ', ') as Licenses,
			STRING_AGG(sl.SoftwareLicenseID, ', ') as LicenseIDs
		FROM dbo.SoftwareLicense as sl
			JOIN dbo.SoftwareTitleLicense as stl ON stl.SoftwareLicenseID = sl.SoftwareLicenseID
		WHERE sl.LicenseTypeID IN (25,43)
		GROUP BY stl.SoftwareTitleID
	) as ibmTitles ON ibmTitles.SoftwareTitleID = tins.SoftwareTitleID
	JOIN dbo.SoftwareTitle as st ON st.SoftwareTitleID = ibmTitles.SoftwareTitleID
	JOIN dbo.ComplianceComputerStatus as ccs ON ccs.ComplianceComputerStatusID = cc.ComplianceComputerStatusID
	JOIN dbo.AssetStatus as ast ON ast.AssetStatusID = a.AssetStatusID
ORDER BY tins.ValidTo DESC, cc.ComplianceComputerID, st.FullName