Assistance With Colour Coding

[CyberCitizen]

Hi Guy's,

I have a report based on BIOS hardware age. Wanting to use this to target our laptop replacements.

I have the odd colour reports but not sure how to colour this one based on the Years Old column.

Basically wanting it green up until 3 years old, yellow from 3 years old to 3 and a half years then red from 3 and a half years and up.

We have currently are replacing every 4 years, but there is a drive to replace every 3 years after the warranty is out and donate to charity.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  Convert(nVARCHAR(10),tblBIOS.ReleaseDate,101) As [BIOS Date],
  Cast(Round(DateDiff(day, tblBIOS.ReleaseDate, GetDate()) / 365.00,
  2) As Numeric(8,2)) As [Years Old],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblADusers.Username As Username1,
  tblADusers.email
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblState.Statename = 'Active'
Order By [Years Old] Desc

[CyberCitizen]

Just wanted to say thank you, this was exactly what I was after.

[nnewton · Accepted solution]

SELECT TOP 1000000 tblAssets.AssetID, 
                   tblAssets.AssetName, 
                   tblAssets.Username, 
                   tblAssets.Domain, 
                   tblAssets.IPAddress, 
                   tsysIPLocations.IPLocation, 
                   tblAssetCustom.Manufacturer, 
                   tblAssetCustom.Model, 
                   tsysOS.OSname AS OS, 
                   COALESCE(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) AS icon, 
                   CONVERT(NVARCHAR(10), tblBIOS.ReleaseDate, 101) AS [BIOS Date], 
                   CAST(ROUND(DATEDIFF(day, tblBIOS.ReleaseDate, GETDATE()) / 365.00, 2) AS NUMERIC(8, 2)) AS [Years Old], 
                   tblAssets.Lastseen, 
                   tblAssets.Lasttried, 
                   tblADusers.Username AS Username1, 
                   tblADusers.email,
                   CASE
                       WHEN CAST(ROUND(DATEDIFF(day, tblBIOS.ReleaseDate, GETDATE()) / 365.00, 2) AS NUMERIC(8, 2)) < 3
                       THEN '#d4f4be'
                       WHEN CAST(ROUND(DATEDIFF(day, tblBIOS.ReleaseDate, GETDATE()) / 365.00, 2) AS NUMERIC(8, 2)) < 3.5
                       THEN '#fff1ad'
                       ELSE '#ffadad'
                   END AS backgroundcolor
FROM tblAssets
     INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
     INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
     LEFT JOIN tsysIPLocations ON tsysIPLocations.LocationID = tblAssets.LocationID
     INNER JOIN tblState ON tblState.State = tblAssetCustom.State
     LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
     INNER JOIN tblBIOS ON tblAssets.AssetID = tblBIOS.AssetID
     INNER JOIN tblADusers ON tblADusers.Username = tblAssets.Username
WHERE tblState.Statename = 'Active'
ORDER BY [Years Old] DESC;