Select Top 1000000 tblAssets.AssetID,
Case
When tblassets.Version Like '%5.5%' and cast(tblassets.BuildNumber as bigint) >= 9919047 Then '#00FF00'
When tblassets.Version Like '%6.0%' and cast(tblassets.BuildNumber as bigint) >= 15517548 Then '#00FF00'
When tblassets.Version Like '%6.5%' and cast(tblassets.BuildNumber as bigint) >= 20502893 Then '#00FF00'
When tblassets.Version Like '%6.7%' and cast(tblassets.BuildNumber as bigint) >= 20497097 Then '#00FF00'
When tblassets.Version Like '%7.0%' and cast(tblassets.BuildNumber as bigint) >= 21053776 Then '#00FF00'
When tblassets.Version Like '%8.0%' and cast(tblassets.BuildNumber as bigint) >= 20842819 Then '#00FF00'
Else '#F0E68C'
End As backgroundcolor,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblassets.Version,
tblassets.BuildNumber,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblVmwareInfo.BiosVersion,
tblVmwareInfo.BiosDate,
tblAssets.Processor,
tblAssetCustom.PurchaseDate,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblState.Statename As State
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Order By tblAssets.AssetName