Computer age report with categories.

[Daniel_Red]

Hi I need some help with my computer age report.
I would like to based on the years old have a new Column called "Risk".

• So that if a computer is 0 years old = "Unknown age".
  
• So that if a computer is 0.001 to 4 years old = "Compliant Device" and row color Green.
  
• So that if a computer is 4 to 8 years old = "Medium Risk Device" and row color yellow.
  
• So that if a computer is 8 and up years old = "Riskiest Risk Device" and row color red.
  

This is what I got.
Thank you for your help

Age report

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.PurchaseDate,
  Case
    When tblAssetCustom.PurchaseDate Is Not Null Then DateDiff(day,
      tblAssetCustom.PurchaseDate, GetDate()) / 365.0
    Else '0'
  End As [Years Old],
  tblAssetCustom.Warrantydate,
  tblADusers1.Name As [Managed by],
  tblADusers.Name As [Last user],
  tblAssets.Lastseen,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tblOperatingsystem.Caption,
  tblADComputers1.OU
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblADComputers tblADComputers1 On tblAssets.AssetID =
    tblADComputers1.AssetID
  Inner Join tblADusers tblADusers1 On tblADComputers1.ManagerADObjectId =
    tblADusers1.ADObjectID
  Inner Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.Manufacturer Not Like '%VMware%' And
  tblAssetCustom.Model Not Like '%virtual%' And tblAssetCustom.State = 1 And
  tblComputersystem.Domainrole <= 1
Order By [Years Old] Desc

[RCorbeil]

Add two columns:

  CASE
    WHEN tblAssetCustom.PurchaseDate IS NULL THEN 'Unknown Age'
    WHEN DateDiff(day, tblAssetCustom.PurchaseDate, GetDate()) / 365.0 < 4 THEN 'Compliant'
    WHEN DateDiff(day, tblAssetCustom.PurchaseDate, GetDate()) / 365.0 < 8 THEN 'Medium'
    ELSE 'Riskiest'
  END AS Risk,
  CASE
    WHEN tblAssetCustom.PurchaseDate IS NULL THEN NULL  -- no colour
    WHEN DateDiff(day, tblAssetCustom.PurchaseDate, GetDate()) / 365.0 < 4 THEN '#d4f4be'  -- green
    WHEN DateDiff(day, tblAssetCustom.PurchaseDate, GetDate()) / 365.0 < 8 THEN '#ffd152'  -- yellow
    ELSE '#ffadad'  -- red
  END AS backgroundcolor,

The ranges you specified overlap at 4 and 8 years, so I chose to assume those were the low point the next-higher group, i.e. 0-3.999..., 4-7.999..., 8+.