Select distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblOperatingsystem.Caption As OS,
tblsoftwareuni.softwareName,
tblSoftware.softwareVersion,
Case
When GetDate() >= subquery1.[EOL Date] Then 'EOL'
When GetDate() < subquery1.[EOL Date] Then 'EOL in ' + Cast(DateDiff(DAY, GetDate(),
subquery1.[EOL Date]) As nvarchar) + ' days'
Else 'Supported'
End As Status,
subquery1.[EOL Date],
Case 
When tblErrors.ErrorText Is Not Null Or 
tblErrors.ErrorText != '' Then 
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg 
Else '' 
End As ScanningErrors,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When GetDate() >= subquery1.[EOL Date] Then '#ffadad'
When GetDate() < subquery1.[EOL Date] Then '#d4f4be'
End As backgroundcolor
From tblAssets
Left join (Select tblsoftware.AssetId,
Case
When tblsoftware.softwareVersion Like '3.5%' Then '2018-04-10'
When tblsoftware.softwareVersion Like '4.0%' Then '2021-07-13'
Else '2016-04-12'
End As [EOL Date]
From tblsoftware
inner join tblsoftwareuni on tblsoftware.softID = tblSoftwareUni.SoftID
where tblSoftwareUni.softwareName Like '%SQL Server Compact%') As
subquery1 On subquery1.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner join tblSoftware on tblSoftware.AssetId = tblAssets.AssetID
Inner join tblSoftwareuni on tblSoftwareuni.SoftID = tblSoftware.softID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID, 
Max(tblErrors.Teller) As ErrorID 
From tblErrors 
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID = 
ScanningError.ID 
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller 
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype = 
tblErrors.ErrorType 
Where tblState.Statename = 'Active' and tblSoftwareUni.softwareName Like '%SQL Server Compact%'
Order By tblAssets.Domain,
tblAssets.AssetName