Notification

Icon
Error

Assistance With Colour Coding

Posted: Tuesday, July 28, 2020 7:34:08 AM(UTC)
CyberCitizen

CyberCitizen

Member Original PosterPosts: 377
0
Like
This issue has been solved! Click here to view the solution
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.

Code:
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
nnewton
#1nnewton Member Posts: 20  
posted: 7/28/2020 8:52:59 AM(UTC)
Code:
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;
CyberCitizen
#2CyberCitizen Member Original PosterPosts: 377  
posted: 8/7/2020 6:20:38 AM(UTC)
Just wanted to say thank you, this was exactly what I was after.

Active Discussions

Installer Windows 10 Upgrade
by  CyberCitizen  
Go to last post Go to first unread
Last post: 7/8/2020 1:07:47 AM(UTC)
Installer Installer - Microsoft Office
by  Florian_Eigsi   Go to last post Go to first unread
Last post: 6/29/2020 3:33:20 PM(UTC)
Installer Firefox ESR 68.8.0 MSI Installer
by  PLSJohnJohn  
Go to last post Go to first unread
Last post: 5/11/2020 4:33:50 PM(UTC)
Installer Update Chrome browser with GoogleUpdate.exe ?
by  Inna Ptushkina   Go to last post Go to first unread
Last post: 5/4/2020 8:39:29 PM(UTC)
Installer Bios Update for Dell all in one
by  Florian_Eigsi  
Go to last post Go to first unread
Last post: 4/2/2020 11:36:43 AM(UTC)
Installer Uninstall - Adobe Acrobat 9x
by  palemmo   Go to last post Go to first unread
Last post: 3/25/2020 7:25:05 PM(UTC)
Installer OneDrive Silent Install
by  Steven.C  
Go to last post Go to first unread
Last post: 3/13/2020 2:55:19 PM(UTC)