cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
CyberCitizen
Honored Sweeper
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
1 ACCEPTED SOLUTION
nnewton
Engaged Sweeper III
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;

View solution in original post

2 REPLIES 2
CyberCitizen
Honored Sweeper
Just wanted to say thank you, this was exactly what I was after.
nnewton
Engaged Sweeper III
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;